First let me say, that this is a very simplistic explanation of what was to me, a very complex and difficult problem and solution. I realize that my explanation of the active directory/Kerberos aspects of this are particularly simplistic, but while I’m a fairly competent DBA, I’m a rank novice when it comes to network security. Because of this I welcome any polite questions, corrections, or criticisms, and I will do my best to respond to them. Second, this is targeted to those individuals who are having a problem with linked servers and want a simplified “cookbook” approach to fixing it.

The Symptom:

I've created a linked server on SQLServer A pointing to SQLServer B. I check that connections should be made using the current security context and I log in using windows authentication as MyDomain/MyLogin. I have sufficient permissions on both servers, but whenever I try to run a query pointing to SQLServer B from SQLServer A, I get this crazy message “Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server conn”. I don’t get it, I’m not logging in as ‘(null)’ so why am I getting this message?

The Problem:

If a user wishes to connect to a remote SQL Server, they can be authenticated by either a login associated with an active directory account or a SQL Server login. When a user logs into a SQL Server from their workstation this is considered a single “Hop”. The complication comes in when after connecting to this remote instance of SQL Server, the user wishes to authenticate to a 2nd instance of SQL Server. This is the 2nd “Hop”.

User Computer >HOP> SQL Server A > HOP > SQL Server B

Quick note: If the user remotes to the server that SQL Server A is on and then tries to authenticate to SQL Server B this is still only a single “Hop”. Only 2 locations are involved.

SQL Server A > HOP > SQL Server B

If the authentication uses a SQL Server login then there is no second “hop”. The user and password specified by the linked server are used to authenticate to the target server. If however, the authentication involves an active directory account, then things get a bit more complicated. In order for two SQL Server instances to communicate using an active directory they need to use Kerberos security. An explanation of Kerberos is beyond the scope of this article and honestly my current knowledge; however, I can say that in order for Kerberos to work correctly, a SPN (Service Principal Name) for each instance and a “trust” between the service accounts the two SQL Servers are using, must be created. If the service account that SQL Server (2005+) is running under has the correct permissions, then SQL Server will create the SPN automatically when the instance starts, and remove it as the instance is shut down. Kerberos uses the SPNs, among other things, to make the interactions between the two locations work. And of course as with all things security, a relationship between the two service accounts has to be explicitly allowed, this being the “Trust”.

Now the Solution:

1. Make sure that the SQL Server instances, SQLServerA and SQLServerB, each have their own service accounts; say SVCSQLServerA and SVCSQLServerB. Each instance should have its own service  account.
2. You must grant Read and Write ServicePrincipalName¹ for both instances. This is what allows SQL Server to create & remove SPNs.
3. Assign the service accounts as the startup accounts for the SQL Server instances. SVCSQLServerA for SQLServerA and SVCSQLServerB for SQLServerB.
4. Restart the SQL Server Instances. This causes a SPN to be created (Service Principal Name) for each instance.
5. Once this is done a "Delegation" tab will be visible in AD for each of the service accounts. Grant both of your service accounts "Trust this user for delegation to any service (Kerberos only)". ¹
6. Now if your security people balk at the "any service" part, like mine did, they can grant the trust just to the other service account.

   i.e. For SVCSQLServerA grant "Trust this user for delegation to specified services only"/"Use Kerberos only"/SVCSQLServerB and vice versa. ²

¹ One or both of these grants will require a domain administrator.

² The SPN is created using the port number that the SQL Server instance is associated with. This isn’t a problem if you are able to get “Trust this user for delegation to any service (Kerberos only)”. However, if you have to grant the trust to a specific other service account then it will actually be granted to the SPN, which, if you are keeping up includes the port number. Now what happens if you re-start SQL Server and a new port is assigned? (Dynamic Ports being the default for named instances) You guessed it, your “Trust” is no longer valid. The best way to avoid this is to set your SQL Servers to use a fixed port.

A list of safe ports can be found here: http://www.iana.org/assignments/port-numbers

At this point you should be able to hop between ServerA and ServerB freely. 

Two quick notes, 1) If DNS resolution doesn’t work in both directions, Kerberos negotiation will fail and none of the above will help.  2) This solution only works for SQL Server instances 2005 and above.  The solution for 2000 instances as I understand it is similar but involves manually creating the SPNs.