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:
What I have tried:
- 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.
Submit an article or tip