25

I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs created for the service accounts that run SQL Server on each of the servers in order to enable Kerberos. I've done that and both now show the authentication scheme to be Kerberos, however, I'm still facing the error:

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'".

In Active Directory, I can see that the service account for ServerB is trusted for delegation to MSSQLSvc, but I noticed that the service account for ServerA does not yet have "trust this user for delegation" enabled. Does the target server also need to have that option enabled? Is anything else necessary to be able to use the current Windows login to use a linked server?

Improve this question

2 Answers 2

Reset to default
27

Every machine in the chain from your desktop to the server you are calling has to be Kerberos enabled for the trust to advance past the first hop. So, yes the server needs to trust the user for delegation.

The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" almost always indicates a delegation problem.

  • Your Windows Account must have access to both ServerA and ServerB.
  • You must not have the setting "Account is sensitive and cannot be delegated."
  • Both ServerA and ServerB must have their own SPN registered.
  • The servers must be TCP/IP or named pipes connected.

The SQL Server Books Online article that offers some more detail is "Configuring Linked Servers for Delegation": http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx

Improve this answer
3
  • 1
    Usefull MSDN Blog to details about SPN and Kerberos
    – Jan Zahradník
    Commented Apr 8, 2015 at 11:55
  • Also check if you are using a DNS alias for either server, the SPN's needs to match.
    – Greg
    Commented Nov 22, 2017 at 0:03
  • Does each Windows user account have to be specifically set up in the SQL logins, or can a domain group be used?
    – Brain2000
    Commented Nov 10, 2020 at 20:15
0

Two things here:

  1. When using a linked server without mapping the local server login to the remote server do not login to a remote machine and use a Windows login to run scripts. The double hop issue comes into play since it uses impersonation.

  2. Also, you will need to setup the local DTC through DCOMCNFG for communication. See attached picture.

(DCOMCNFG -> Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> LocalDTC -> Properties -> Security)

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.