The Problem

If you have been using linked servers to query other SQL Servers in your environment, you may have had trials and errors getting them working correctly. If your network uses Kerberos authentication, this adds another layer of complexity. Congratulations, you got them configured and they work. Your users are happy. Most of the resources I found on the internet researching this problem, referred to this error when first setting up a linked server and making sure delegation and such was set properly. This post isn’t about setting up linked servers. This is about when the linked server that has been working just fine for months stops working and gives you this error:

    
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.224.150.65]

What to look for

If this was after a reboot, look at the beginning of the Errorlog and look for this message: 

    
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
That’s the normal message. Next if you see this message, then the SPN is indeed your problem.

    
The SQL Server Network Interface library could not register the Service Principal Name (SPN)  MSSQLSvc/MyServer.myhouse.local:1433 ] for the SQL Server service. Windows return code: 0x200e, state: 20. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

To get to the errorlog you can use SSMS and look under Management->SQL Server Logs in Object Explorer.

You can run the following query: 

    
        
USE master
GO
xp_readerrorlog 0, 1, N'SPN', NULL, NULL, NULL, N'asc'
/*
1. Log number
2. Log type (1 = Errorlog, 2 = SQL Agent Log)
3. Search string
4. 2nd search string (applied to first string results)
5. Start time
6. End timd
7. Sort order.
*/
or you can used dbatools PowerShell module Get-DbaErrorLog to find it with: 
    
        
Get-DbaErrorLog -SqlInstance MyServer -LogNumber 0 -Text 'SPN'

How to fix it

The brute force way to fix is with restarting SQL Server. On startup it will try again to register the SPN. The better way is using the command line tool SETSPN. You need to run the following in an Administrator CMD or PowerShell windows using an account that has permission to write SPN information to Active Directory. 

 
     
        
# one with the port
setspn -A MSSQLSvc/MyServer.myhouse.local:1433 myhouse\MySQLServiceAcct
# one without
setspn -A MSSQLSvc/MyServer.myhouse.local myhouse\MySQLServiceAcct
If you’re not running on port 1433, be sure to change appropriately. Depending on the size of your network, this may take a few minutes to filter through.

If you get this, you didn’t use your admin account 😃 

    
Failed to assign SPN on account 'CN=YourServiceAcct,OU=YourOU,OU=YourOU,OU=YourOU,DC=YourDC,DC=YourDC,DC=YourDC', error 0x2098/8344 -> Insufficient access rights to perform the operation.

Summary

So hopefully this helps you. Better yet, hopefully you don’t run into this and you don’t need it. This was a real life problem I ran into this past weekend. I shut down our SQL Server instances for the a scheduled network outage. The outage was roughly 3 hours. When it was over I started everything back up and everything looked clean.

It wasn’t until later that users complained about the error they were getting and I had to dig in to figure out what happened. I suspect that when I was bringing up SQL, some of the network resources weren’t quite ready yet, which caused the problems in registering the SPNs.

Thanks for reading.