You may experience connectivity issues to SQL Server if SPNs are misconfigured

Applies to: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Express


When your application tries to connect to SQL Server using Kerberos authentication the connection may fail with various error messages. One of these error messages may be the following:
Cannot Generate SSPI Context


A Service Principal Name (SPN) for the SQL Server Database Engine may either be missing, misplaced, or is a duplicate to other SPNs configured in the Active Directory of the domain.

Note: A misplaced SPN is a SPN that is configured on the wrong account in the Active directory.


Note: The setspn.exe and klist.exe programs ship with Windows Server 2008 and later. For previous Windows versions you may need to download these separately from the Microsoft Download Center or obtain it from the Windows Support Tools package.

Note: For additional information on each of the resolutions discussed in this section refer to the More Information section of this article.

Case 1: How to resolve a Missing SPN:
  1. Run the following command to add the missing SPN
    setspn –A <SPN> <Account>
  2. Try reconnecting to SQL Server with your client application.
Alternatively, you could provide the proper permissions to the SQL Service Account to allow SQL to auto generate the SPNs needed.  See more information section for details.

Note: A Missing SPN may not result in a connectivity failure but will prevent the application from using Kerberos authentication.

Case 2:How to resolve a Misplaced SPN:
  1. Run the following command to remove the misplaced SPN
    setspn –D <SPN> <Account>
  2. On the client machine, either logoff and log back in or clear the Kerberos ticket cache by running the following command
    klist purge
  3. Try reconnecting to SQL Server with your client application.
Note: If you wish to use Kerberos from your application you need to configure the correct SPN using the resolution from Case 1 above.

Case 3:How to resolve a duplicate SPN:

  1. Identify the SPNs that are duplicate and must be removed.
  2. Run the following command to remove each of the duplicate SPNs:
    setspn –D <SPN> <Account>
  3. On the client machine, either logoff and log back in or clear the Kerberos ticket cache by running the following command
    klist purge
  4. Try reconnecting to SQL Server with your client application.

More Information

The following naming conventions will help with the resolution steps:
  • <SPN> has the format of MSSQLSvc/<FQDN of computer>:<port> | <Instance Name>
    <port> is always used for SPN associated with the TCP Protocol
    <Instance Name> is used for SPN associated with the Named Pipes protocol. While Shared Memory will also cause this same type of SPN to be auto generated, it is not used.
  • <Account> is one of the following:
    The name of the computer if the service account is LocalSystem, NetworkService, or LocalService
    The name of the domain account used for the service account (Format is <domain>\<account>)

Auto SPN Generation for SQL Server:
If you are using a Domain User account for the SQL Service, you can grant the following privileges to the account in the Active Directory, to allow SQL Server to auto create the needed SPNs on the Service Account:
  • Read servicePrincipalName
  • Write servicePrincipalName

Important We recommend that you do not grant WriteServicePrincipalName right to the SQL service account when the following conditions are true:
  • There are multiple domain controllers.
  • SQL Server is clustered.
In this scenario, the SPN for the SQL Server may be deleted because of latency in Active Directory replication. This may cause connectivity issues to the SQL Server instance.

Assume that you have the following:
  • A SQL virtual instance named Sqlcluster with two nodes: Node A and Node B.
  • Node A is authenticated by domain controller A and Node B is authenticated by domain controller B.

The following may occur:
  1. The Sqlcluster instance is active on Node A and registered the SQL SPN in domain controller A during start up..
  2. The Sqlcluster instance fails over to Node B when Node A is shutdown normally.
  3. The Sqlcluster instance deregistered its SPN from domain controller A during the shutdown process on Node A.
  4. The SPN is removed from domain controller A but the change has not yet been replicated to domain controller B.
  5. When starting up on Node B, the Sqlcluster instance tries to register the SQL SPN with domain controller B. Since, the SPN still exists Node B does not register the SPN.
  6. After some time, domain controller A replicates the deletion of the SPN (from step 3) to domain controller B as part of Active Directory replication. The end result is that no valid SPN exists for the SQL instance in the domain and hence you see connection issues to the Sqlcluster instance.

Note This issue is fixed in SQL Server 2012.

The following resources have more information about SPN and SQL Server: