SQL SERVER – Could Not Register Service Principal Name Persists – Notes from the Field #105

Have you ever seen the error below in your SQL Server log shortly after startup?  You’ll actually see two of them and you can see the difference between them in the screen shot, but here is the text.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/Node1.stars.com:1433 ] 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.  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.

So what causes this error and how can you fix it?  The first thing to note is that it is an informational message and not actually an error.  There are two scenarios in which you will see this message.

1. The first scenario is what you see right out of the box.  SQL Server does not have the rights on a domain service account to register Service Principal Names (SPNs).  The description we see here is very clear in telling us that SQL Server could not register the required SPNs.  It even tells us the exact SPN syntax it attempted to register.  If you want to use Kerberos you have to register the SPNs manually or give the service account the right to perform the change itself.  If you decided to register them manually, then now is a good time to write down the SPNs from the description.
2. The second scenario is a weird one that throws people off.  If you choose to manually register the SPNs on the service account and restart SQL Server, you’ll still see the same message in the log.  Now why in the world would this message even show up if you already registered the SPNs?  In fact, many folks will see this message and assume they are not using Kerberos, because the message clearly states that it could not register the SPNs.  The assumption is usually that they got the SPN syntax wrong or that the SPNs never got registered.

Just for kicks, let’s jump back over to my test server and take a look at the current connections.  Most folks will add a WHERE clause to the following query to just look at their current connection, but I’m going to caution you about that.  If you’re on the server itself, you won’t get accurate results because you end up using Named Pipes unless it’s disabled.  We are looking to see if there are any Kerberos connections at all so we don’t want to filter the result set.

SELECT * FROM sys.dm_exec_connections
--WHERE session_id = @@SPID

Well that’s an interesting result, huh?  I clearly have Kerberos connections despite the message I keep getting in the SQL Server log.  So why is that?  Well it comes down to the semantics of the message.  The message said it couldn’t register the SPNs and that’s true.  It couldn’t register them because you already did it.  So if you ever see this message, make sure you go look at your connections first (using the above query) to see if it is something you need to address or not.  If you see KERBEROS in the auth_scheme column, then you are all set.

If you want the message to go away completely, there is only one way to do that.  You have to give the account running the SQL Server service the permissions to change its own SPNs.  You can do that by opening the properties of the account and heading to the security tab.  You will find an account in the account list called SELF.  Grant that account “Write to Public Information”, restart the SQL Server service, and the message will disappear.  Now you’ll see a new message stating that SQL Server was able to successfully register the required SPNs.

Reference: Pinal Dave (https://blog.sqlauthority.com)