locked
SQL 2012 Linked Server, Delegation and Managed Service Accounts RRS feed

  • Question

  • I'm running into the double hop problem trying to create a linked server between Server A: a SQL Server 2012 default instance , using a Managed Service Account and Server B: a SQL Server 2008 default instance using a domain user service account.

    Here's what I've done so far

    1.  Verfied that the SPNs have been successfully registered on both servers.  The servers have the following entries in their logs:

    Server A

    The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ServerA.MyDomain.com ] for the SQL Server service.
    The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ServerA.MyDomain.com:1433 ] for the SQL Server service.

    Server B

    The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ServerB.MyDomain.com ]

    The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ServerB.MyDomain.com:1433 ]

    2.  I got the error: Login Failed for user 'NT Anonymous Logon' when creating a linked server on Server A to Server B. 

        Remembering that I must grant delegation to Server A's service account I changed the value for the userAccountControl in Active Directory to :

         0×1001000 = ( WORKSTATION_TRUST_ACCOUNT | TRUSTED_TO_AUTHENTICATE_FOR_DELEGATION );  

         I then deleted the SPN on Server A, restarted the SQL service on Server A and confirmed successful registration of the SPN in the SQL Server log.

    3.  Running this query: select auth_scheme from sys.dm_exec_connections where session_id=@@spid on both machines returns Kerberos. 

    Unfortunately I'm still getting the 'NT Anonymous Logon' error. 

    Any thoughts?

    Thanks

    Scot



    • Edited by scootersays Tuesday, February 12, 2013 5:49 PM
    Tuesday, February 12, 2013 5:47 PM

Answers