none
Cant connect to linked server

    Question

  • I'm running into the double hop problem with my linked server but I've tried everything I can find to work around it and nothing seems to help. I am at a loss to why the double hop with Windows authentication is not working.

    Background

    I'm using Windows authentication in all circumstances. My personal domain account is an administrator on all SQL servers on the network.

    I can connect from my PC to SQL Server A. I can connect from my PC to SQL Server B. Both SQL Servers use the same domain service account to start up.

    Logging on from my PC to SQL Server A, I created a linked server to SQL Server B using the current login's security context. It will not authenticate. I get "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)". The ERRORLOG on SQL Server B shows "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Error: 18456, Severity: 14, State: 11."

    If I RDP into server A and run SSMS from there, the same linked server works just fine.

    What I've Checked So Far

    Both servers are trusted for delegation in AD.

    Both servers use the same domain service account to start up. It is not marked as "sensitive" and it is trusted for delegation in AD.

    The user account (mine) is not marked as "sensitive" in AD.

    If I map my domain account to a SQL Server account in the linked server properties, the linked server works fine. If I set it to "use current users security contect" (which is what I want) it fails to authenticate with sql server B.

    The error log for sql server b records this error message when the server starts up - "The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0xd, state: 13. ...".  I dont know why this is happening. Do I need to grant any special permissions to the service account or computer object to prevent this?

    setspn was run manually to register MSSQLSvc\ServerB:1433 and MSSQLSvc\ServerB(with FQDN):1433



    Chuck

    Friday, April 06, 2012 3:29 PM

Answers

  • I think I've finally got it figured out. Maybe someone can explain this to me. Everything I've read online about registering SPNs manually says they get registered against the computer object in AD for the host where sql server runs and that's what I was trying to do.

    example:

    setspn -A MSSQLSvc/<servername>.<myDNSdomain>:<servername>

    But for the servers where  Kerberos authentication was working (which is required for double-hop to work), the SQL Server's SPN is not being registered with the computer object, but rather the user object for the service's startup account. So I deleted all of the SPNs I'd registered with computer objects and registered them with the service account's user object and suddenly it all started working!

    Why does everything I read online including much of Microsoft's own documentation say to use the computer object when  SQL itself does it's automatic SPN registration against the service account's user object? Is this because I'm not using the default service account when I install SQL and use a domain account instead?

    Is it better to use the default account? If I use the default account will I still be able to do things like run backups to a network share?


    Chuck

    • Marked as answer by chuckh1958 Thursday, April 19, 2012 1:21 PM
    Thursday, April 12, 2012 1:28 PM

All replies

  • Hi,

    SPN is required for your SQL Server to get linked servers working. If you wish to allow the database engine service account to create SPN automatically you need to assign proper permission. It is explained at here: http://technet.microsoft.com/en-us/library/cc773257(WS.10).aspx#BKMK_Delegating

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Friday, April 06, 2012 7:23 PM
  • I granted the missing permission to the service account and restarted one of my SQL Servers. The ERRORLOG now says that the SPN is successfully registered, but if I run "setspn -L hostname" it does not show the SPN. My linked servers still only work on single hop. Double hop still fails.

    Chuck

    Friday, April 06, 2012 8:11 PM
  • Hi,

    Please check these posts for troubleshooting: 

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Wednesday, April 11, 2012 1:37 PM
  • I think I'm getting closer to the root of the problem. One of those links mentioned that for double-hop to work you must be using Kerberos authentication. A query of dm_exec_connections shows my session is using NTLM for almost every server (but not all) on my network. What would cause this?

    Here's an example. For BISGSQL10 I'm getting NTLM. For BISGSQL11 and BISGSQL12 I'm getting KERBEROS. All are clustered SQL servers (same MSCS cluster actually). The setspn output is identical for all and none show the MSSQLSvc references. Yet I can connect to BISGSQL11  using Kerberos and create a link to BISGSQl12 and it works.

    U:\>setspn -l bisgsql10
    Registered ServicePrincipalNames for CN=BISGSQL10,OU=Servers,OU=SG,DC=biodoma05,DC=bi,DC=corp:
            MSServerClusterMgmtAPI/BISGSQL10.biodoma05.bi.corp
            MSServerClusterMgmtAPI/BISGSQL10
            MSClusterVirtualServer/BISGSQL10.biodoma05.bi.corp
            MSClusterVirtualServer/BISGSQL10
            HOST/BISGSQL10.biodoma05.bi.corp
            HOST/BISGSQL10

    U:\>setspn -l bisgsql11
    Registered ServicePrincipalNames for CN=BISGSQL11,OU=Servers,OU=SG,DC=biodoma05,DC=bi,DC=corp:
            MSServerClusterMgmtAPI/BISGSQL11.biodoma05.bi.corp
            MSServerClusterMgmtAPI/BISGSQL11
            MSClusterVirtualServer/BISGSQL11.biodoma05.bi.corp
            MSClusterVirtualServer/BISGSQL11
            HOST/BISGSQL11.biodoma05.bi.corp
            HOST/BISGSQL11

    U:\>setspn -l bisgsql12
    Registered ServicePrincipalNames for CN=BISGSQL12,OU=Servers,OU=SG,DC=biodoma05,DC=bi,DC=corp:
            MSServerClusterMgmtAPI/BISGSQL12.biodoma05.bi.corp
            MSServerClusterMgmtAPI/BISGSQL12
            MSClusterVirtualServer/BISGSQL12.biodoma05.bi.corp
            MSClusterVirtualServer/BISGSQL12
            HOST/BISGSQL12.biodoma05.bi.corp
            HOST/BISGSQL12





    Chuck



    • Edited by chuckh1958 Wednesday, April 11, 2012 4:19 PM
    Wednesday, April 11, 2012 4:18 PM
  • Hi,

    How have you got the linked servers set-up?  I've just had big problems because a colleague had used the machines IP Address rather than its name in the linked server properties, which caused kerberos issues... presumably because it didn't match the registered SPN.

    In any case, I've never needed to set the servers themselves to trusted for delegation.  I've always done something like:

     - On a computer in the same domain as SQL Server A and SQL Server B, run

               setspn -A MSSQLSvc/SQLSERVERA.mydomain.com:INSTANCENAME domain\user

               setspn -A MSSQLSvc/SQLSERVERA.mydomain.com:portnumber domain\user

               setspn -A MSSQLSvc/SQLSERVERB.mydomain.com:INSTANCENAME domain\user

               setspn -A MSSQLSvc/SQLSERVERB.mydomain.com:portnumber domain\user

    - Alternatively, make the domain account running the services, i.e. domain\user a temporary domain admin, then delete the SPN's for the domain\user account with setspn -d MSSQLSVC....., restart SQL Server and it will automatically register valid SPN's

    Run Active Directory console,

    Search for 'domain\user'

    Select the 'Delegation' tab

    Select 'Trust this user for delegation to specified services only' and 'Use Kerberos only'

    Select 'Add'

    Select 'Users or Computers'

    type in 'domain\user' and select 'Check Names'

    select 'OK'

    From the list of services, add the MSSQLSvc service type for ServerA and ServerB

    Select 'OK'

    Select 'OK' back at the domain\user properties

    You might need to log the domain\user accound in and out.

    Then, as you say, you can query sys.dm_exec_connections for the auth_scheme, i.e. select auth_scheme from sys.dm_exec_connections where session_id = @@spid (sorry if syntax is wrong- doing this from memory)

    Worked a treat for me...



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Wednesday, April 11, 2012 4:43 PM
  • I'm constantly adding new servers so I really don't want to have to add the individual service each time I add a new server. Instead on the delegation tab for the service account I've selected "trust this user for delegtion to any service (kerberos only)".

    On a few servers it's working and I'm getting Kerberos authentication. If I link a server between two of those, it works, double-hop and all.

    I can't for the life of me figure out why so many of my servers are only giving me NTLM authentication though, which seems to be the root of the linked server problem.

    All my SQL servers use the same domain account to start up (which is also a member of Domain Admins btw). Their "setspn -l" outouts all look the same. And they are all saying in the ERRORLOG that they sucessfully registered their SPN's the last time they started. Yet I still get NTLM authentication on most and that's preventing the double-hop from working.


    Chuck

    Wednesday, April 11, 2012 6:35 PM
  • I think I've finally got it figured out. Maybe someone can explain this to me. Everything I've read online about registering SPNs manually says they get registered against the computer object in AD for the host where sql server runs and that's what I was trying to do.

    example:

    setspn -A MSSQLSvc/<servername>.<myDNSdomain>:<servername>

    But for the servers where  Kerberos authentication was working (which is required for double-hop to work), the SQL Server's SPN is not being registered with the computer object, but rather the user object for the service's startup account. So I deleted all of the SPNs I'd registered with computer objects and registered them with the service account's user object and suddenly it all started working!

    Why does everything I read online including much of Microsoft's own documentation say to use the computer object when  SQL itself does it's automatic SPN registration against the service account's user object? Is this because I'm not using the default service account when I install SQL and use a domain account instead?

    Is it better to use the default account? If I use the default account will I still be able to do things like run backups to a network share?


    Chuck

    • Marked as answer by chuckh1958 Thursday, April 19, 2012 1:21 PM
    Thursday, April 12, 2012 1:28 PM
  • Hi Chuck,

    Do you have an example of the documentation?

    As per: http://msdn.microsoft.com/en-us/library/ms191153.aspx, the example Microsoft gives is (same as I posted previously):

      setspn -A MSSQLSvc/myhost.redmond.microsoft.com:1433 accountname

    When I have problems with manual registration, I temporarily grant the service account running SQL Server domain admin privileges (I think you can just grant permissions to writeserviceprincipalname and readserviceprincipalname in AD).

    Sorry - what you say default service account, do you mean LocalSystem?



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Thursday, April 12, 2012 4:03 PM
  • I cant find where I thought I read that you should specify the computername so I'll retract that claim. :) It was probably on a non-microsoft site. Lord knows how many sites I hit trying to research this problem.

    Yes by the default account I mean LocalSystem. I've noticed when I start SQL with that account, the spn gets registered with the computer name.


    Chuck

    Thursday, April 12, 2012 5:27 PM
  • hmm, are you able to provide an example?  I must admit, I don't think I've tested it with SQL running under the localsystem account, but I'd be surprised if it didn't register in the same way a domain account would, or manual registration does.  In fact, looking at the documentation again, I can't see anything that would account for this behavior.  Strange.


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Thursday, April 12, 2012 9:20 PM
  • had a thought about this on the way into work this morning.  The computer name doesn't appear as "myserver$" does it?  In particular, the $ sign on the end of the machine name?  That would explain a few things.  I've seen many applications, mainly that generate login failures with "myserver$".  All because local system on the machine translates as myserver$ on the network (I'm sure there's a more technical explanation than this).

    If you register the SPN using the local system account, I'm fairly certain that you'd see a kerberos connection on a single-hop to that machine.  Then, as per your problem, you double-hop to the next server you get the login failure.  Presumably (I'll try and find a link to corroborate this), because the serverA local system account doesn't have access to serverB.  Chaning to a domain account with permissions on both servers obviously doesn't have this problem, so the double-hop works.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you


    Friday, April 13, 2012 9:14 AM
  • In the case of a server starting up using "local system account" the server name is pcd0385. Here is the output of "setspn -l pcd0385"

    U:\>setspn -l pcd0385 Registered ServicePrincipalNames for CN=PCD0385,OU=Workstations,OU=PA,DC=biodoma05,DC=bi,DC=corp:         MSSQLSvc/PCD0385.biodoma05.bi.corp:1433         MSSQLSvc/PCD0385.biodoma05.bi.corp         TERMSRV/PCD0385.biodoma05.bi.corp         TERMSRV/PCD0385         RestrictedKrbHost/PCD0385         HOST/PCD0385         RestrictedKrbHost/PCD0385.biodoma05.bi.corp         HOST/PCD0385.biodoma05.bi.corp


    Chuck

    Friday, April 13, 2012 3:41 PM
  • I think that SPN for the computer object is probably a hangover from when you were registering them manually.

    As a test, if you deleted the SPN's registered against the computer object PCD0385, i.e.

    setspn -D MSSQLSvc/PCD0385.biodoma05.bi.corp:1433 PCD0385

    setspn -D MSSQLSvc/PCD0385.biodoma05.bi.corp PCD0385

    Then deleted the SPNs registered against the domain user account you use, for this server, i.e.

    setspn -D MSSQLSvc/PCD0385.biodoma05.bi.corp:1433 mydomain\mysuer

    setspn -D MSSQLSvc/PCD0385.biodoma05.bi.corp mydomain\mysuer

    That should start you off with a clean slate.

    You could either:

    - Restart SQL Server with a domain account that has rights to register SPN's in AD, or

    - Manually register the SPN against the user account that's running the SQL Server services, i.e.

    setspn -S MSSQLSvc/PCD0385.biodoma05.bi.corp:1433 mydomain\myuser

    setspn -S MSSQLSvc/PCD0385.biodoma05.bi.corp mydomain\myuser

    You shouldn't have to register SPN's against the computer object.  Just checked mine, and none of them do and kerberos double-hops are working fine.  It's explained here: http://msdn.microsoft.com/en-us/library/ms191153.aspx

    "A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used"

    As you're delegating to any service, it should pick it up...



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Friday, April 13, 2012 4:43 PM
  • Nope. If I simply shut down the instance (the one that uses local system account), the SPN gets deleted. This SPN is definitely being registered and unregistered automatically by SQL.

    After stopping the instance...

    U:\>setspn -l pcd0385
    Registered ServicePrincipalNames for CN=PCD0385,OU=Workstations,OU=PA,DC=biodoma05,DC=bi,DC=corp:
            TERMSRV/PCD0385.biodoma05.bi.corp
            TERMSRV/PCD0385
            RestrictedKrbHost/PCD0385
            HOST/PCD0385
            RestrictedKrbHost/PCD0385.biodoma05.bi.corp
            HOST/PCD0385.biodoma05.bi.corp

    There's no more MSSQLSvc SPNs. I don't recall if I mentioned that the version here is 2008 R2. That might be relevant to whats going on.


    Chuck

    Friday, April 13, 2012 4:54 PM