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