SQL SERVER - Could Not Register Service Principal Name Persists - Notes from the Field #105 - SQL Authority with Pinal Dave (2024)

SQL

9 Comments

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)

9 Comments. Leave new

• 

  Dave Percy

  June 30, 2016 10:58 pm

  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

  • 

    Pinal Dave

    May 22, 2017 5:34 pm

    You have to do that do domain controller – Domain User.

    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

  • 

    Pinal Dave

    April 22, 2018 7:28 am

    Use https://support.microsoft.com/en-in/help/2985455/kerberos-configuration-manager-for-sql-server-is-available and set the SPNs.

    Reply

• 

  Sanketh Salian

  November 25, 2019 11:32 am

  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

• 

  georgey

  March 15, 2023 9:52 am

  thank you, this helped me alot

  Reply

• 

  Nas

  March 6, 2024 3:21 pm

  Always made my work easy with the tips

  Reply