Having made use of linked servers in a few previous posts, let us take a closer look at a common problem with them: double-hop authentication failure when using Windows Authentication. This issue presents itself most commonly when a user connects to a SQL server, but then is unable to use a linked server connection to connect to a second SQL server, even though the user has the necessary permissions to make the connection. This applies even when the linked server is configured to pass the login’s current security context to the target server.
If, rather than passing through Windows Authentication, remote login credentials are specifically defined in the linked server, then the connection should succeed, but… you also have a potentially large security hole! It could be as bad as an entirely un-secured username and password if the credentials were specified in a provider string; to investigate:
-- Select information about linked servers
SELECT name, data_source, provider_string
WHERE server_id <> 0
Or it could be something potentially even more difficult to manage, as might be the case if you have many logins that need to be controlled so as NOT to have access to the linked server. Very quickly it could get messy indeed.
But, back to using Windows Authentication with pass-through login context….
Consider the scenarios shown in the graphic below to better understand the problem.
The first row shows an end user can connect to a SQL Server (SQL A) using SQL Server Management Studio (SSMS) running on their local machine.
On the second row we see that the same user can also connect in the same way to a second SQL Server (SQL B).
Furthermore, on row three we see that the user can connect to SQL A via a terminal and use a linked server connection that is defined on SQL A to connect to SQL B. So far so good.
However, when the user tries a “double-hop” by connecting from a local machine to SQL A and then from SQL A to SQL B through the linked server… then the connection fails. Generally the user will receive error 18456: “login failed for anonymous logon”.
The problem, in this case, is actually rather easy to understand and resolve. What happens in our final example is that SQL A does not pass credentials on to SQL B, and hence SQL B rejects the connection of what it sees as an anonymous user.
Why doesn’t SQL A pass through the credentials? Because the service account underwhich SQL A is running was not configured to do so!
There is a “Delegation” tab on the active directory (AD) user account properties whereby this behavior can be specified. The default is “Do not trust this user for delegation”, which means do not pass credentials through. But there is also a setting to “Trust this user for delegation to any service (Kerberos only)” which will allow credentials to be forwarded. You could also get more granular and specify individual services to be trusted.
I’ve too often heard “you can’t use Windows Authentication with linked servers” as an excuse for SQL login proliferation. Let’s put a stop to that, if we can. SQL logins are necessary in certain cases, but in general we’d like as few as possible for both ease of management across the enterprise as well as enhanced security management.