Sql-server – Kerberos Issue – Anonymous Login

authenticationkerberossql serverssmswindows

I have a linked server that connects one SQL Server to another SQL Server. For two weeks now there has been an error when testing the connection;

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (.Net SqlClient
Data Provider)

I have posted about this issue when I first received the user was complaining about it. The projected solution was to manually register the SPN. Our environment already has SPN registered to the service account names for each of the servers. Below are the ways me and my team troubleshooted:

  • Used the Kerberos Configuration tool to check if there were any issues with SPNs. Yes there was in fact an issue with multiple SPNs which were shown as "Misplaced" in the Kerberos tool. So we updated the SPN's and the status returned to "Good"

  • Checked CMD -setspn -l to view the list of the services and the service we are looking for with the correct port number is one of the service account under the SPN.

  • Restarted the SQL Server services to make sure the updates take full effect.

  • Used DMV to find out the authentication scheme being used. Kerberos authentication is being used.

  • Queried the data the user has requested to see that the anonymous issue is still present.

  • Tested the connection to the linked server and the anonymous issue is still present.

  • Checked the error log to see an error that states:

The SQL Server Network Interface library could not register the SPN
[XXXX] for the SQL Server service. Windows return code: 0x2098, state
15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos.

I have notified the Windows AD team to check the delegation status. He has confirmed that the delegation is set to: 'Trust this user for delegation to any service (Kerberos only)'

I am running out of ideas on how much further I can troubleshoot or where the problem lies. If anyone has any other idea, please feel to share and give some feedback. What else can I do to resolve this issue? This issue has been persistent for 2 weeks now. And the users really need to access the data they need for their daily work. Thank you in advance.

Best Answer

Could be this. Part of Security Patch that came out in Nov. & was updated in Dec.

This explains the problem: https://support.microsoft.com/en-us/topic/managing-deployment-of-kerberos-s4u-changes-for-cve-2020-17049-569d60b7-3267-e2b0-7d9b-e46d770332ab

This contains the patch under Security Updates header: https://msrc.microsoft.com/update-guide/vulnerability/CVE-2020-17049 (first 1 is for 2016 & up)

First, make sure all AD Servers are on the same patch. Create "enforce modes" create reg key if not there. Set to 1. Let sit 7 days to reset. Change to reg key to 2. Restart Service or wait another 7 days.

Related Question