1

I'm facing a very specific issue on my SQL Server instance. Recently, I had to create a Linked Server (referencing server B) in my server A, to connect to a database in B through A. Everything worked and works well when I'm in the server A (accessing via remote desktop).

I need to query the server A through an Apache application, it always works. But when I query server B through A using Apache (not in remote desktop, where it works), the query fails, so I open SSMS and execute the same query and it returns the following error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGIN'

What really concerns me is that it's not an authentication error (even though the message says it), because when I access the server A via RD, execute the query and come back to the Apache application, then I am able to query, which makes no sense at all.

It looks like it's an intermittent issue, and sometimes I just can't force it.

2 Answers 2

2

Likely your two servers are not properly setup correctly either with SPNs or Kerberos delegation, which are usually the two root factors at play for this kind of error and the symptoms you mentioned. When a Linked Server object works when directly queried from ServerA to ServerB but returns the error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGIN'" when queried from a third machine, that's usually a sign of a double hop issue as a result from one of the aforementioned root causes.

Please see my answer to the same kind of question recently posted on here, where it was a Kerberos issue. Also please see my related question where I also exhibited a Kerberos issue myself. There's a ton of information on troubleshooting these issues, among each of the answers posted there.


One workaround to this issue, when one doesn't want to deal with Kerberos authentication, is to setup your Linked Server to use a dedicated SQL Login, as noted in my linked DBA.StackExchange answer:

Alternatively, if you changed that to "Be made using this security context" and entered the credentials for a SQL Login (as opposed to a Windows Authentication Login) that has access to server A, then that would solve your issue too. Because now, no longer would it try to pass-through the currently authenticated user's security context, rather it would use the one of the specified SQL Login. This makes it no longer a double hop, from a security perspective.

The downside to that solution is that you have to ensure the SQL Login you choose has the appropriate access on server A that supports all your use cases of the Linked Server. Anyone who uses that Linked Server will now be accessing server A as the SQL Login that you entered the credentials for in your Linked Server object. You have to be careful drawing the line between over- and under-provisioning that SQL Login's permissions on server A.

0

This is a delegation-related issue first and foremost as it requires additional setup beyond typical Kerberos (which tends to happen automagically as SPNs are registered/deregistered at startup/shutdown) connections, but that doesn't mean you don't have issues related to Kerberos as well, but cross that bridge only if you come to it.

Be warned, there are a number of different types of delegation one can employ in their environment, so I'll point you to my answer on another dba.se question outlining what those are, how to properly configure them, as well as some pitfalls associated with each. While that question is related to Linked-Servers, the approach for delegation outlined in that answer will be the same as you need here. Comment if you have any questions.

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.