How the Cannot generate SSPI context error was fixed

Last week on one of the production instances no one was able to connect to SQL Server using Windows Authentication. All the connections were failing with the following error.

Cannot generate SSPI context

Since the domain controller to which this server was connected is known to have connectivity issues, it was decided to restart the SQL Server instance so that. After stopping the SQL Server instance failed to get started. Here is the error message with which it was failing.

This KB article nicely explains many of the reasons why we would get “Cannot generate SSPI context” error of which an incorrect or non-existent SPN is one of the reasons.  As evident from the error message the service was not starting due to some issues with the SPN (Service Principal Name). Before I start writing about how this issue was fixed, let us try to get some information about SPN.

What is Service Principal Name (SPN)?

SPN is a unique identifier for each service that is running on servers. With the help of SPN the clients which try to connect to the service can easily identify it. SPN for each service is registered in the Active Directory. SPNs can be registered under a Computer account or as a user account in Active Directory. The SPN for a service is created in the following format.

<service name>/<FQDN of the server>:<port number>

MSSQL/servername.domain.com:1433

How is SPN created?

When a service starts, the service tries to create the SPN (if it does not exist already) under the credentials of the service start up account. If the service is configured to run under machine accounts (Local System, Network service), SPN is created under a Computer Account  in AD. If the service is configured to start using a domain user account, the SPN is created under the user account in Active Directory.

If the service is starting under a domain account, that account should have Domain Administrator privilege in the Active Directory. Else the creation of the SPN will fail when the service starts. It is not a good security practice grant service accounts with Domain Administrator privilege. SPN can be manually added using the setspn.exe utility.

What was the root cause of this error?

As I mentioned earlier, from the error message it was clear that the issue was a result of errors with the SPN. The output of the existing SPN listing for the SQL Server service account is as below.

The SPN for the service account was wrongly set as MSSQLSvc/<domain name> instead of MSSQLSvc/<computername.domainname>. Once this was confirmed, the old SPN entry was deleted by using the -D switch in setspn.exe and the correct SPN was created by using the following command.

setspn –A MSSQLSvc/<servername.domainname> accountname

After the correct SPN was created, SQL Server service started successfully.

3 thoughts on “How the Cannot generate SSPI context error was fixed

  1. Matt

    To allow the startup account to automatically modify SPNs as non domain admin you need to give ReadServicePrincipalName and WriteServicePrincipalName the correct permissions.

    Ask your domain admin or if you are one, follow the steps below.

    1. The ADSIEdit tool is included in the Windows Support Tools
    2. Click Start, click Run, type Adsiedit.msc, and then click OK. (must be a domain admin)
    3. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName , and then click Properties.
    4. In the CN= AccountName Properties dialog box, click the Security tab.
    5. On the Security tab, click Advanced.
    6. 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.
    7. Under Permission entries, click SELF, and then click Edit.
    8. In the Permission Entry dialog box, click the Properties tab.
    9. 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
    10. Click OK two times.

    As seen here…
    http://www.mskbarticles.com/index.php?kb=319723

    Reply
  2. Pingback: Something for the Weekend – SQL Server Links 09/09/11

  3. col

    If you need to run the SQL Server service under a domain account but are getting this error you can:
    (1) Temp add your SQL Server service account to Domain Admins
    (2) Restart the SQL Server services on your server
    This will allow the SPN to be created.
    (3) Remove the SQL Server service from Domain Admins.
    You should now be able to connect even when SQL Server is restarted as the SPN is only created once.

    Reply

Leave a Reply