Kerberos double hop not working on new workstations

Mika PW Nyberg 6 Reputation points
2022-04-25T20:33:15.98+00:00

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 :-(

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,759 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2022-04-25T21:42:18.19+00:00

    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.

    0 comments
  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2022-04-26T01:51:46.56+00:00

    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".

  3. cthivierge 4,056 Reputation points
    2022-06-02T12:16:11.503+00:00

    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.

    https://www.sqlshack.com/how-to-link-two-sql-server-instances-with-kerberos/

    There is no host entry in the host file of the old client computer ?

    hth

    0 comments
  4. KE-9542 1 Reputation point
    2022-10-11T10:13:25.68+00:00

    Perhaps the Defender credential Guard settings.

    0 comments