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”
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.
- 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.
- 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 !!!