SPN Registration Failure Issue


While I was configuring my lab machine, I observed the SPN registration was failed on one of my server with the below error.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/KRISHNA02.SKGLAB.LOCAL ] 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.

Over the last one year, I had worked multiple times on this issue. So when this occurs on my lab machine, I thought of documenting the same. Prior installing SQL Server on my machine, I had created the SPN using the SETSPN tool; however it seems the registration didn’t work as expected. To begin with, I wanted to check the SPN’s currently registered with the SQL Server service account, by executing the command: setspn -l “SKGLAB\SQLSvc-KRISHNA02”

image

From the above screenshot, I found the SPN are registered with the service account. As a next step, I need to check whether the SQL Server service account is allowed to register & un-register the SPN’s in active directory, which is not a default behaviour.

Steps to Follow

Follow the steps mentioned below to allow the SQL Server service account to register the SPN automatically.

  • Open Active Directory Users and Computers, clicks on Views, select Advanced Features.
  • Open the properties of the service account, & go to Security tab.

image

  • Click on Advanced tab, click on Add. On the new permission page, select Principal as “Self”, and set Type as “Allow” & Applies to “This object only”, as shown in the below screenshot.

image

  • From the list of properties available below, select the two properties high lighted below & click Ok twice of apply these properties.

a. Read msDS-PrincipalName
b. Write msDS-PrincipalName

  • Stop the SQL Server service & start it again. Open the SQL Server error log, & verify the status of SPN Registration.

2015-03-26 21:58:27.170    Server    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.
2015-03-26 21:58:27.190    Server   
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/KRISHNA02.SKGLAB.LOCAL ] for the SQL Server service.
2015-03-26 21:58:27.190    Server    The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/KRISHNA02.SKGLAB.LOCAL:1433 ] for the SQL Server service.

SPN registration is successful now.

Happy Learning Smile !!!

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Deployment, How To, Security in SQL Server, Setup LAB, SPN, SQL Server and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s