How to troubleshoot the "Cannot generate SSPI context" error message

• You are connecting to Microsoft SQL Server.
• You are using Integrated Security.
• Kerberos authentication is used to perform the security delegation.

Understanding Kerberos terminology and Service Principal Name

• NTLM over Named Pipes (not using Security Support Provider Interface [SSPI])
• NTLM over TCP/IP sockets with SSPI
• Kerberos authentication over TCP/IP sockets with SSPI

Why Security Support Provider Interface uses NTLM or Kerberos authentication

• ServiceClass: This identifies the general class of service. This is always MSSQLSvc for SQL Server.
• Host: This is the fully qualified domain name DNS of the computer that is running SQL Server.
• Port: This is the port number that the service is listening on.
  

MSSQLSvc/SQLSERVER1.northamerica.corp.mycompany.com:1433

• MSSQLSvc/SQLSERVER1:1433
• MSSQLSvc/123.123.123.123:1433
• MSSQLSvc/SQLSERVER1.antartica.corp.mycompany.com:1433
  
• MSSQLSvc/SQLSERVER1.dns.northamerica.corp.mycompany.com:1433

C:\>ping SQLSERVER1

Pinging SQLSERVER1 [123.123.123.123] with 32 bytes of data:

Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128

Ping statistics for 123.123.123.123:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum =  0ms, Average =  0ms 

C:\>ping -a 123.123.123.123

Pinging SQLSERVER1.northamerica.corp.mycompany.com [123.123.123.123] with 32 bytes of data:

Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Ping statistics for 123.123.123.123:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum =  0ms, Average =  0ms

C:\> 

SQL Server Service Principal Name creation

Simplified explanation

Verify the domain

1. You must make sure that you can successfully log on to Windows by using the same domain account and password as the startup account of the SQL Server service. For example, the SSPI error may occur in one of the following situations:
   
   • The domain account is locked out.
   • The password of the account was changed. However, you never restart the SQL Server service after the password was changed.
2. If your logon domain differs from the domain of the computer that is running SQL Server, check the trust relationship between the domains.
3. Check whether the domain that the server belongs to and the domain account that you use to connect are in the same forest. This is required for SSPI to work.
4. Use the Account is Trusted for Delegation option in Active Directory Users and Computers when you start SQL Server.
   
   
   Note The 'Account is Trusted for Delegation' right is required only when you are delegating credentials from the target SQL server to a remote SQL server such as in a double hop scenario like distributed queries (linked server queries) that use Windows authentication.
5. Use the Manipulate Service Principal Names for Accounts (SetSPN.exe) utility in the Windows 2000 Resource Kit. Windows 2000 domain administrator accounts or Windows Server 2003 domain administrator accounts can use the utility to control the SPN that is assigned to a service and an account. For SQL Server, there must be one and only one SPN. The SPN must be assigned to the appropriate container, the current SQL Server service account in most cases and the computer account when SQL Server starts with the local system account. If you start SQL Server while logged on with the LocalSystem account, the SPN is automatically set up. However, if you use a domain account to start SQL Server, or when you change the account that is used to start SQL Server, you must run SetSPN.exe to remove expired SPNs, and then you must add a valid SPN. For more information, see the "Security Account Delegation" topic in SQL Server 2000 Books Online. To do this, go to the following Microsoft website:
   http://msdn2.microsoft.com/en-us/library/aa905162(SQL.80).aspx
   For more information about Windows 2000 Resource Kits, go to the following Microsoft website:
   http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/default.mspx?mfr=true
6. Verify that name resolution is occurring correctly. Name resolution methods may include DNS, WINS, Hosts files, and Lmhosts files. For more information about name resolution problems and troubleshooting, click the following article number to view the article in the Microsoft Knowledge Base:
   
   
   169790 How to troubleshoot basic TCP/IP problems
   
   
7. For more information about how to troubleshoot accessibility and firewall issues with Active Directory, click the following article numbers to view the articles in the Microsoft Knowledge Base:
   
   
   291382 Frequently asked questions about Windows 2000 DNS and Windows Server 2003 DNS
   
   
   224196 Restricting Active Directory replication traffic and client RPC traffic to a specific port

Configure the SQL Server service to create SPNs dynamically for the SQL Server instances

1. Click Start, click Run, type Adsiedit.msc, and then click OK.
2. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName, and then click Properties.
   
   Notes
   • DomainName is a placeholder for the name of the domain.
   • RootDomainName is a placeholder for the name of the root domain.
   • AccountName is a placeholder for the account that you specify to start the SQL Server service.
   • If you specify the Local System account to start the SQL Server service, AccountName is a placeholder for the account that you use to log on to Microsoft Windows.
   • If you specify a domain user account to start the SQL Server service, AccountName is a placeholder for the domain user account.
3. In the CN= AccountName Properties dialog box, click the Security tab.
4. On the Security tab, click Advanced.
5. 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.
6. Under Permission entries, click SELF, and then click Edit.
7. In the Permission Entry dialog box, click the Properties tab.
8. On the Properties tab, click This object only in the Apply onto list, and then make sure that the check boxes for the following permissions are selected under Permissions:
   • Read servicePrincipalName
   • Write servicePrincipalName
9. Click OK three times, and then exit the ADSI Edit snap-in.

• There are multiple domain controllers.
• SQL Server is clustered.

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

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.

Verify the server environment

1. Kerberos authentication is not supported on Windows 2000-based computers that are running Windows Clustering unless you have applied Service Pack 3 (or a later version) to Windows 2000. Therefore any attempt to use SSPI authentication on a clustered instance of SQL Server might fail. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   235529 Kerberos authentication support on Windows 2000-based server clusters
   
   
2. Verify that the server is running Windows 2000 Service Pack 1 (SP1). For more information about Kerberos authentication support on Windows 2000-based servers, click the following article number to view the article in the Microsoft Knowledge Base:
   267588 "Cannot generate SSPI context" error message is displayed when you connect to SQL Server 2000
   
   
3. On a cluster, if the account that you use to start SQL Server, SQL Server Agent, or full-text search services changes, such as a new password, follow the steps that are provided in the following Microsoft Knowledge Base article:
   239885 How to change service accounts for a clustered computer that is running SQL Server
   
   
4. Verify that the account that you use to start SQL Server has the appropriate permissions. If you are using an account that is not a member of the Local Administrators group, see the "Setting up Windows Services Accounts" topic in SQL Server Books Online for a detailed list of permissions that this account must have:
   http://msdn2.microsoft.com/en-us/library/aa176564(SQL.80).aspx

Verify the client environment

1. Make sure that the NTLM Security Support Provider is installed correctly and enabled on the client. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   269541 Error message when you connect to SQL Server if the Windows NT LM Security Support Provider registry key is missing: "cannot generate SSPI context"
   
   
2. Determine whether you are using cached credentials. If you are logged on to the client by using cached credentials, log off the computer and then log back on when you can connect to a domain controller to prevent the cached credentials from being used. For more information about how to determine whether you are using cached credentials, click the following article number to view the article in the Microsoft Knowledge Base:
   242536 User is not alerted when logging on with domain cached credentials
   
   
3. Verify that the dates on the client and the server are valid. If the dates are too far apart, your certificates may be considered invalid.
4. SSPI uses a file that is named Security.dll. If any other application installs a file that uses this name, the other file may be used instead of the actual SSPI file. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   253577 Error: 80004005 - MS ODBC SQL Server driver cannot initialize SSPI package
   
   
5. If the operating system on the client is Microsoft Windows 98, you must install the Client for Microsoft Networks component on the client.
   
   For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   267550 BUG: "Assertion failed" when you connect to a SQL Server through TCP/IP
   
   

Verify the client network utility

1. On the General tab, the way protocols are defined varies according to the MDAC version. With earlier versions of MDAC, you can select a "default" protocol. On the latest versions of MDAC, you can enable one or more protocols with one at the top of the list when you connect to SQL Server. Because SSPI applies only to TCP/IP, you can use a different protocol, such as Named Pipes, to avoid the error.
2. Check the Alias tab in the CNU to verify that an alias is defined for the server that you are trying to connect. If a server alias is defined, check the settings for how this computer is configured to connect to SQL Server. You can verify this by deleting the alias server to see whether the behavior changes.
3. If the alias server is not defined on CNU, add the alias for the server to which you are connecting. When you perform this task, you are also explicitly defining the protocol and optionally defining the IP address and the port.

Manually set up a Service Principal Name for SQL Server

1. Generate a sqldiag report from SQL Server. For more information, see the "sqldiag Utility" topic in SQL Server Books Online.
2. Capture a screen shot of the error on the client.
3. Open a command prompt on the node that cannot connect to SQL Server, and then type the following command:
   net start > started.txt
   This command generates a file that is named Started.txt in the directory where you run the command.
4. Save the values for the registry key under the following registry key on the client computer:
   HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER\CLIENT\CONNECTTO
5. In a clustered environment, find the value of following registry key for each node of the cluster:
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\LSA\LMCompatibilityLevel
6. In a clustered environment, see whether the following registry key exists on each cluster server node:
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NTLMSsp
7. Capture the results if you connect to SQL Server by using a Universal Naming Convention (UNC) name (or the SQL Network Name on a cluster) from the client.
8. Capture the results if you ping the computer name (or the SQL Network Name on a cluster) from the client.
9. Save the name of the user accounts that you use to start each one of the SQL Server services (MSSQLServer, SQLServerAgent, MSSearch).
10. The support professional must know whether SQL Server is configured for Mixed Authentication or Windows Only Authentication.
11. See whether you can connect to the computer that is running SQL Server from the same client by using SQL Server Authentication.
12. See whether you can connect by using Named Pipes protocol.

References