SQL Server 2008 R2 - Access Failure in a query that uses Linked server

Question

We have 2 SQL Server 2008 R2 machines.

A user is running a query on Server A that joins a table in Server B. In the query he is using linked server to point Server B.

User has access to Server B Database in question. He is a db_owner there. but when he runs the query it fails.

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The linked server's security is set to "Be made using the login's current security context". Since the user has db_owner access to Server B database, I would expect this query work from Server A. It doesn't.

How can give this user access to run this query on Server A.

Thanks for your help.

• Hannah Vernon: Have you setup kerberos authentication? dba.stackexchange.com/questions/30121/…
• Aaron Bertrand: Obviously he is not connecting as him - otherwise the login wouldn't be attempted as NT AUTHORITY\ANONYMOUS LOGIN. I mean, unless he knows the password for that account, and that's who he's logging in as - I suspect his credentials are not being followed through...
• Hannah Vernon: Not being followed through because Kerberos authentication is not setup? I seem to recall you must have Kerberos setup for double-hop authentication to work ? Am I wrong?
• Hannah Vernon: TechNet thinks you need SPNs etc for delegation to work between linked servers; technet.microsoft.com/en-us/library/ms189580(v=SQL.105).aspx @Aaron - since I'm certain I'm preaching to the choir here, I can only assume I must be missing something about this. I had linked servers display the Anonymous Login message prior to having Kerberos correctly configured in SQL Server 2005 and 2012.
• JoseTeixeira: Was reading this. Named Pipes might also solve the problem you are facing. Other than this you must not have the setting "Account is sensitive and cannot be delegated" checked.

Answer 1

A number of comments asked if Kerberos was configured correctly, but I do not see a direct response to that issue. Based on the comments that you make I suspect Kerberos is not set up correctly.

Greatly simplified description:

1. If UserAccount is directly logged onto Server A then a linked server to Server B is one hop away and the UserAccount's authentication will be recognized on Server B. This works with or without Kerberos delegation.

2. Is UserAccount is directly logged onto his desktop and accesses Server A then Server A is one hop away from the desktop and the UserAccount's authentication will be recognized on Server A. However, if from the desktop he runs a query against Server A that uses a linked server to access Server B that is two hops away from his desktop. Without Kerberos (or some similar service) the UserAccount's delegation cannot make the second hop and, despite the rights granted on Server B, will not authenticate on Server B.

This is all about how NTLM and Kerberos work. There is a blog from a few years ago that offers some details on Linked Servers and NTLM or Kerberos authentication:

Link

One edited quote is: "In a single-hop setting, windows NTLM authentication, which is available if all machines are windows, is sufficient for delegation; while in a double-hop setting, Kerberos authentication is necessary for the user’s credential to cross machine boundaries from the client to the linked server."

Now, suppose that you do indeed have Kerberos configured properly and still are mystified by the behavior. In that case you might need to check into whether there is a duplicate SPN. If so that will need to be corrected.

The following blog discusses duplicate SPNs and other Kerberos issues: http://blogs.technet.com/b/askds/archive/2013/07/01/interesting-findings-on-setspn-x-f.aspx

Note: Some people just fall back to using Named Pipes instead of TCP/IP, but this is not a recommended solution any more.

Bottom line: Resolve the authentication delegation problem and everything should work.