Removing the Linked Server 2 hop Limitation

I was recently asked to take a quick look at the linked server strategy at my present employer. We were going to use explicit impersonation because we were getting the infamous 2 hop limitation, where you cannot span two servers when using Windows authentication with linked servers (details below). We also did not want to use SQL authentication because that allows users access to the servers on the other end of the linked servers. This occurs when using Windows authentication and the following linked server setting:

I left the Server Options as all defaults.

This would occur when from my laptop, I would then connect to Server1 and issue the following query:

SELECT TOP 10 *
    FROM SERVER2.MASTER.SYS.OBJECTS

I would get this error:

"Login Failed for user 'NT Authority\ANONYMOUS' LOGON"

This error would be returned up when we have a connection from a client logged into a SQL server that has a linked server to another server. In this example, I logged into SERVER1 from my laptop via SSMS and issued a query using a linked server to SERVER2. The above error would be thrown.

This is called the 2 hop rule\limitation. If that client connection moved one hop up (ie. If I logged into SERVER1 via remote desktop\PC Duo) and ran the query; it would run fine. Essentially, we are trying to work around a deeper issue which is our authentication infrastructure is not implemented fully.

Fix Kerberos First

The above error is impossible to get around using NTLM (NT Lan Manager) authentication because delegation is not an option. NT Lan Manager was the authentication protocol used in Windows NT and Windows 2000. While this error may appear to be simply a limitation in using Windows authentication with linked servers, it is actually a limitation of the authentication protocol the client is connecting as.

By default, SQL will attempt to establish a connection using the Kerberos authentication protocol. This is the default protocol used by Windows 2003 and above. If Kerberos authentication fails, the connection will revert to NT Lan Manager (NTLM). This error is a limitation to NT Lan Manager authentication, not linked servers. I can see my authentication protocol by issuing the query:

SELECT auth_scheme
 FROM sys.dm_exec_connections
 WHERE session_id = @@SPID

My result:

Kerberos, if implemented correctly can ameliorate this limitation. So, the first thing is that you need to fix Kerberos authentication. To do that:

SPNs (Server Principal Names) need to be generated for all servers involved. This can be automatic by giving the AD account that is used as the service account the extended permission “write public information”. SPNs are created on SQL Service restart and created under the service account for the service account. A restart may not be an option for production servers, so the Kerberos Configuration Manager can be used to manually create the SPNs under the service account without a restart. Alternatively, you could use the SETSPN utility via the command prompt. You still grant permission to your service account, but these are a couple of ways to get around the need for a restart.
The service account for SQL should be in the same domain as the computer account. If not, trust relationships and permissions for the service account need to be able to create the SPNs in the other domain. Not a requirement, but does help to eliminate the variables. If cross domains are a must, just verify that the service account has the permission to create SPN’s in the domain hosting the computer account for the server you are setting up.
Verify DHCP, DNS, and AD communications are working correctly. Duplicate PTR records for the same IP, incorrect A Host and CNAME records can make authentication a pain to troubleshoot. PTR records should be pointing to the A Host record for a given IP address along with the CNAME records. This is something you will most likely need to work with your system administrators on. What I typically do is do the research using the DNS administrative tool (if I have access) and\or the NSLOOKUP utility.
Verify you are connecting using KERBEROS with this query:
```
SELECT auth_scheme
 FROM sys.dm_exec_connections
 WHERE session_id = @@SPID
```
You should get:

If not, verify SPNs are created via the SETSPN utility or Kerberos Configuration Manager. If not, then it’s probably just a permission issue with the account. If the SPN’s are created, it may just be your credential cache, so you can either restart the machine you are connecting from or clear the credential cache:
Disconnect your SQL session
Open cmd prompt and type “klist.exe purge”
Connect and retry the above query

Once you have established that KERBEROS is working on both the server you are connecting to and the server the linked server is pointing to, you need to grant delegation to the service account on the server that owns the linked server:

The above image is for the testsql account in my AD domain. For testing, I chose the selection “Trust this user for delegation to any service”. Alternatively, you could choose a more secure route by choosing the 3^rd choice. You would just need to input a little more information.

NOTE: If the delegation tab does not exist, you need to create a SPN for the account first, then refresh, and the delegation tab should be there.

Testing

Just to recap, the initial error that I was getting was "Login Failed for user 'NT Authority\ANONYMOUS' LOGON". After some investigation, I realized that this was a symptom of a higher level issue dealing with our authentication method (NT Lan Manager). First, I verified that I was using NT Lan Manager (NTLM) as my authentication protocol. I then worked on implementing Kerberos authentication by setting AD user permissions for the service account, creating SPN’s (Server Principal Names), verifying my infrastructure with the help of the system administrators, and setting up delegation for the AD account.

Client Connection: My Laptop using my AD credentials
Server with linked server: SERVER1 with a linked server called SERVER2 configured with the above configuration in the picture above.
Linked server connected to: SERVER2

Running the following query:

SELECT TOP 10 *
 FROM SERVER2.MASTER.SYS.OBJECTS

Gave this error before when using MT Lan Manager Authentication:

"Login Failed for user 'NT Authority\ANONYMOUS' LOGON"

Once Kerberos was properly set up, I issued the same query and received the result set that I wanted:

By implementing the Kerberos authentication protocol, I was able to successfully utilize linked servers without needing to worry about the limitation on where to run a query from.