Kerberos double hop not working on new workstations

Mika PW Nyberg 1 Reputation point

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.
9,734 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 77,496 Reputation points MVP

    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:

    0 comments No comments

  2. CathyJi-MSFT 20,976 Reputation points Microsoft Vendor

    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 3,981 Reputation points

    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 ?


    0 comments No comments

  4. K. Epp 1 Reputation point

    Perhaps the Defender credential Guard settings.

    0 comments No comments