Yesterday I was troubleshooting a rather common problem. A query running on an instance of SQL Server (INST1) was using the OPENROWSET function to retrieve a rowset from another instance (INST2). This is known as “double-hop authentication”, and Kerberos authentication is required for this to work. When executed from a particular client machine, the query was failing with the infamous “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON'” error.
The troubleshooting steps for this problem are well known and are described in multiple sources. My favorite is the My Kerberos Checklist post by Adam Saxton on the CSS SQL Server Engineers blog. I went through the list and confirmed that the basics were configured correctly: the proper SPNs were registered, delegation for the SQL instance service account was enabled, the client account was not marked as sensitive, etc. Then I ran the following query on INST1:
SELECT auth_scheme, client_net_address
FROM sys.dm_exec_connections;
I noticed that for some clients, auth_scheme was KERBEROS, yet for that particular client machine it was NTLM. This seemed to indicate that the instance was correctly configured for Kerberos, and the problem was likely client related.
The next step was to trace the authentication process on the client. This produced the first useful clue – during an attempt to get a Kerberos ticket from the Key Distribution Center (KDC), a call to the LsaCallAuthenticationPackage function failed with substatus 0xc00002fd, which corresponds to this error message: “The encryption type requested is not supported by the KDC.”
I started looking at the all parts of the picture once again, including the service account of the INST1 instance. I noticed that for that account, the “Use DES encryption types for this account” option was enabled, yet that option was disabled for other SQL Server service accounts in the domain. Once I disabled that option and restarted the INST1 instance, the query immediately worked.
The root cause of this problem is explained in KB977321. In Windows Server 2008 R2 and Windows 7, DES encryption is disabled by default, and AES encryption is used. The client machine in this case did run Windows Server 2008 R2, unlike other clients connecting to INST1, which ran older versions of Windows. Since the INST1 service account only supported DES encryption, Kerberos authentication failed because a common encryption type between the client and the server did not exist. Another possible solution could be to enable the (weaker) DES encryption on the client, as described in the KB article.
© 2019 Microsoft. All rights reserved.