locked
Double hop failing RRS feed

  • Question

  • Double hop authentication is failing for one Windows login. For others it works fine. All SQL service accounts and servers are in the same domain as that one user. Both servers are trusted for delegation. Neither the account in question nor the service account is marked as "sensitive - do not delegate".  The linked server security is set to "be made using the current login's security context" and has no mapped users. Using that same account, if I check the authentication scheme in sys.dm_exec_connections it is showing up as Kerberos on both servers. Both servers have been rebooted to ensure all caches are cleared.

    Using another account in the same domain, the double hop works just fine.

    What am I missing?


    Chuck


    • Edited by chuckh1958 Friday, September 18, 2015 2:28 PM Additional information
    Friday, September 18, 2015 2:26 PM

All replies

  • Chuck

    What error are you getting?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, September 20, 2015 7:18 AM
  • Hi chuck, did you check if account has logon restriction verifying "Log On To" in Account properties on AD user object.

    Check also eventual GP applied and also local policy on both server, in "Security Settings -> Local Policies -> User Right Assignment" if there is some kind of difference between users.

    Sunday, September 20, 2015 7:27 AM
  • I'm getting error 18456 but the login name reported in the error is "NT Anonymous login".

    The odd thing is that same user can be authenticated to both both servers directly (single hop) and even get Kerberos authentication.

    That user's AD object is not marked as "sensitive - do no delegate".

    I'm at a loss to explain why the double hop fails, but works for other users in the same domain. If it were a problem with domain trusts, it wouldnt work for any user in that domain, but it does. If the problem were something wrong with the failing user's AD object or the SPNs, we'd never get Kerberos on a single hop connection for that user, but we do.


    Chuck

    Sunday, September 20, 2015 8:23 PM
  • Chuck

    Take a look at the following blog post for details about the State Code meanings of 18456:

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

    Is it state 11? 

    If so,state 11 is valid login but server access failure. State=16 means that the incoming user does not have permissions to log into the target database.  Also check the default database for that login is online. Refer this link for more info, etc..http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 21, 2015 5:38 AM
  • Copied directly from the "advanced information" dialog box.

    Error Number: 18456
    Severity: 14
    State: 1

    The blog says that  state 1 means the login is disabled. The login however is not disabled or I wouldn't be able to use it for the first hop. 


    Chuck

    Monday, September 21, 2015 3:57 PM
  • There are no logon restrictions for the account. There are no group policy differences between users that work for double hop, and the one that does not.

    Chuck

    Monday, September 21, 2015 4:04 PM
  • I would try to drop the login from both servers and recreate.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, September 21, 2015 9:57 PM
  • Just tried that. It didn't help.


    Chuck

    Tuesday, September 22, 2015 4:08 PM