DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 2443457 - Last Review: April 7, 2013 - Revision: 3.0

Symptoms

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

Cause

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.

Resolution

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:



Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use (http://go.microsoft.com/fwlink/?LinkId=151500) for other considerations.

Applies to
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Enterprise Evaluation
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2012 Business Intelligence
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
Keywords: 
KB2443457
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support