The SQL Server Network Interface library could not register the Service Principal Name (SPN)

Question

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.

I am getting above error whenever I restart my SQL service. I changed it from current service account to local account and was able to connect to SSMS from local machine, but if I am using current service account then this error is coming and in SSMS the error is :

The target principal name is incorrect. Cannot generate SSPI context.

The password is not changed for this service account.

Answer 1

You specifically get error "0x21c7, state: 15" when an attempt is made to register the SPN which already exists, but possibly under different account.

When you run SQL Service under local system account, an SPN is registered under the Computer Object in AD. You can confirm from below:

setspn -l ComputerName

In above results, If you see any SPNs for MSSQLSvc then they need to be dropped with below commands on AD (You need to have Domain Admin permissions for this) so that you can then register SPN under new Service account:

setspn -d MSSQLSvc/FQDN:XXXXXXXXXX ComputerName

Once the above is done, restart SQL Service under the Service Account adn it should successfully register the SPN under the new Service account and you will be able to confirm by running the below command:

setspn -l DomainName\LoginName

Reference: http://www.b-blog.info/en/changing-service-account-for-ms-sql-server-2012-and-the-following-issues.html

You could also use Microsoft Kerberos Configuration Manager for SQL Server to automatically detect and fix such SPN Issues:

https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/

Answer 2

According to KB article 811889:

Because you might not use a domain administrator account to run the SQL Server service (to prevent security risk), the computer that is running SQL Server cannot create its own SPN. Therefore, you must manually create an SPN for your computer that is running SQL Server if you want to use Kerberos authentication when you connect to a computer that is running SQL Server.

In the same article, there are several options to remedy this problem. I would try creating an SPN manually.

To manually set up an SPN for SQL Server, refer to KB article 319723 (excerpted below), where it is recommended to use the ADSIEdit tool on the domain controller. Scroll down to Step 3: Configure the SQL Server service to create SPNs dynamically on that article.

You have to modify permissions to allow Read servicePrincipalName and Write servicePrincipalName for the SQL Server service account, and it's quite convoluted.

For what it's worth, sometimes deleting a server name from there gets SPN to work, too. Good luck!

---

The steps from KB 319723 (archived) are:

1. Click Start, click Run, type Adsiedit.msc, and then click OK.
2. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName , and then click Properties.
3. In the CN= AccountName Properties dialog box, click the Security tab.
4. On the Security tab, click Advanced.
5. In the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries.
   • If SELF is not listed, click Add, and then add SELF.
6. Under Permission entries, click SELF, and then click Edit.
7. In the Permission Entry dialog box, click the Properties tab.
8. On the Properties tab, click This object only in the Apply onto list, and then click to select the check boxes for the following permissions under Permissions:
   • Read servicePrincipalName
   • Write servicePrincipalName
9. Click OK two times.
10. In the CN= AccountName Properties dialog box, click Attribute Editor.
11. Under Attributes, click servicePrincipalName in the Attribute column, and then click Edit.
12. In the Multi-valued String Editor dialog box, remove the service principle names (SPNs) for the instances of SQL Server that use this SQL Server service account.
13. Exit the ADSI Edit snap-in.

NOTE: these steps are included for reference as the original KB page has been taken down. Important context & informative notes exist on the linked snapshot that are not excerpted here.

Answer 3

I am able to solve the issue with this post.

https://cmatskas.com/fixing-error-cannot-generate-sspi-context-after-changing-sql-service-account/

The recommendation is to grant the domain account that the SQL Server service is using the necessary AD privileges to read and write Service Principal Names.