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

SQL
7 Comments

[Note from Pinal]: In this episode of the Notes from the Field series database expert Ryan Adams explains a very critical error user receive when registering service principals. Ryan is one guy who spends more time with real world issues with SQL Server than anything else. He has mastered the art of resolving complex errors and document them so easily that one can’t find anywhere else. In this blog post Ryan addresses a very interesting error related to Service Principal Name. Read the experience of  Ryan in her own words.


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.

Solarwinds

SQL SERVER - Could Not Register Service Principal Name Persists - Notes from the Field #105 Kerb Error in Log

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

SQL SERVER - Could Not Register Service Principal Name Persists - Notes from the Field #105 Kerb Connection

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – XEvents to Find Cardinality Estimation Issues
Next Post
SQL SERVER – 2016 – Opening JSON with OPENJSON()

Related Posts

7 Comments. Leave new

  • I found that the above query with the where clause, “where session_id=@@spid”, only show my own session, which returned NTLM. Modified like the query below actually returned what I was looking for.

    select * from sys.dm_exec_connections
    where auth_scheme = ‘KERBEROS’

    Reply
  • James Bailey
    May 20, 2017 3:45 am

    Where is this? What interface?

    “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. “

    Reply
  • sathish chandra
    April 21, 2018 3:41 am

    Hi Pinal, i got same error:
    The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ xxxxxxxxxxxx ] 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.

    But when i ran above query no ‘KERBEROS’ found .

    we are getting below errors as well.
    Could not connect because the maximum number of ‘1’ dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process.

    Error: 17810, Severity: 20, State: 2.

    Can you please provide solution

    Reply
  • How do we go about SPN registration in case of a Always on Enabled server. Do we register the individual server or we register the listener Name. Anyone, kindly share your thoughts.

    Reply
  • Chintan Vithlani
    December 26, 2019 9:59 am

    HI Pinal,
    we have registered SPN against service account in our AD but still error persists, also checked in dm_exec_connections auth_scheme is SQL for all the entries present. Can you Please help further

    2019-12-23 14:03:52.98 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/ABC.global.PQR.com:SQLEXPRESS ] for the SQL Server service. Windows return code: 0x21c7, 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.
    2019-12-23 14:03:52.98 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/ABC.global.PQR.com:portno ] for the SQL Server service. Windows return code: 0x21c7, 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.

    Reply

Leave a Reply

Menu