I can't say that I master Kerberos very well, but I have very much enjoyed listening to David Postlewaite's talks about Kerberos, and at least I learnt some, if not enough to help you. But I very much recommend that you listen to him yourself: https://www.youtube.com/watch?v=oY9-qctTMwQ.
Kerberos double hop not working on new workstations
We are running in awkward problems with our new Windows 10 workstations, where SQL linked server queries don't work anymore. All is set up correctly and linked server logins are working on our current Win10 workstations. But when users are getting new ones, they get ANONYMOUS login errors from linked server. Following facts are checked:
- old and new workstations are same win10 versions, belong to same ad, reside in same OU (hence same GPOs should be applied)
- only base image used for initial installation has been updated
- same version of MS SSMS, same SQL servers, same SQL query, same user account
- login to first SQL Server is using kerberos in both cases
- KLIST shows identical tickets, same Ticket Flags, even same Kdc called
I am running out of ideas, what setting on worksation could prevent Kerberos delegation to work?
Our EUS is outsourced and they are raising hands :-(
Sign in to comment
Sort by: Most helpful
Hi @Mika PW Nyberg ,
> they get ANONYMOUS login errors from linked server
Regarding to above error, the client is probably running under the local system account, and SQL Server has not registered SPN. Thus, NTLM was used.
Since the client and SQL Server are located on different machines, the local system account of the client cannot be authenticated using NTLM, so the identity of the client is regarded as ANONYMOUS LOGON.
The solution is to manually register the SPN under the SQL Server service account so that Kerberos could work normally. Or change your SQL server service account to a domain admin account that has permission to register SPN automatically. In addition, you can also using Microsoft Kerberos Configuration Manager, a diagnostic tool for SQL Server that helps troubleshoot Kerberos related connectivity issues with SQL Server.
Refer to manually register the SPN.
If it is not work, please reading below blog to find if you missed some settings(such as delegation setting and so on) , hope this could help you.
Linked Server Error “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON'”
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Hello, thanks for the answer.
I tried to explain, that all steps are configured regarding SQL servers, SPN:s and AD.
Linked servers work correctly from older Windows 10 workstations, but when same user gets a new workstation, it stops working.
Login to first SQL is however using kerberos also with a new workstation(checked from SQL server sys.dm_exec_sessions and sys.dm_exec_connections tables)
So there must be some setting on new Win workstation that is causing this, just can't figure out what setting...
Hi @Mika PW Nyberg ,
Did you mean that you migrate linked server to new workstation? Or setting new linked server as same as old one in new workstation?
Did you get any error message when you run Microsoft Kerberos Configuration Manager?
No changes in SQL Server environment, user has a new PC.
UserX uses Win1 client machine to connect to SQLSRV-A, SQL query against SQLSRV-A including connection to SQLSRV-A:s linked server SQLSRV-B works ok.
UserX gets a new PC, Win2
UserX uses Win2 client machine to connect to same SQLSRV-A, SQL query against same SQLSRV-A including connection to same SQLSRV-A:s linked server same SQLSRV-B DO NOT work.
However UserX's SQL Login from new Win2 Client to SQLSRV-A is using Kerberos, not NTLM.
I can't recall that there is any setting on the client machine itself that matters. But there may be some registration elsewhere.
Anyway, did you watch David's video? It may give you ideas of where you should look.
video gave nothing new :-(
query from failing client causes server1 to request a ticket for server2 with kdc-options: 40830000, ie. constrained-delegation: True.
There is no such ticket because we are not using constrained delegation (and not wanting to), so kdc returns error-code: eRR-BADOPTION (13), NT Status: STATUS_NOT_FOUND (0xc0000225)
working client gets server1 to request ticket without constrained-delegation bit set, and gets a valid ticket from kdc for server2
I would really like to know the root-cause for this, really no ideas anyone?
are both Win 10 clients on the same patch / build level?
There were a few kerb issues with different windows updates in the past.
Also double check the AD permissions of the client computer accounts regarding the delegation stuff ( ..is trusted to delegate ... can't remember exactly) . Maybe there is a difference.
Sign in to comment
The problem is not the new client computer but the old one. I don't know how it work but it shouldn't...
AFAIK, the only way to do a double hop authentication is to use Kerberos authentication and Kerberos constrained delegation.
There is no host entry in the host file of the old client computer ?
Perhaps the Defender credential Guard settings.