0

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.

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, 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!

2

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/

-2

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.

  • 1
    Thanks for the answer, ExploringApple. Try to include more content than just links as answers, though, since dead links render answers like this useless. – LowlyDBA Nov 10 '17 at 20:59
  • Point noted, thanks for the clarification and agree! – ExploringApple Nov 11 '17 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.