How to troubleshoot the "Cannot generate SSPI context" error message (811889)

SUMMARY

This step-by-step article describes how to troubleshoot the most typical sources of the "Cannot generate SSPI context" error message. You may receive this error message under the following conditions:

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

Understanding Kerberos terminology and Service Principle Name

The SQL Server driver on a client computer uses integrated security to use the Windows security token of the user account to successfully connect to a computer that is running SQL Server. The Windows security token is delegated from the client to the computer that is running SQL Server. The SQL Server driver performs this delegation when the user's security token is delegated from one computer to another by using one of the following configurations:

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

Security Support Provider Interface (SSPI) is a set of Windows APIs that permits delegation and mutual authentication over any generic data transport layer, such as TCP/IP sockets. Therefore, SSPI permits a computer that is running a Windows operating system to securely delegate a user security token from one computer to another over any transport layer that can transmit raw bytes of data.

The "Cannot generate SSPI context" error is generated when SSPI uses Kerberos to delegate over TCP/IP and Kerberos cannot complete the necessary operations to successfully delegate the user security token to the destination computer that is running SQL Server.

Why Security Support Provider Interface chooses NTLM or Kerberos

Kerberos uses an identifier named "Service Principle Name" (SPN). Consider an SPN as a domain or forest unique identifier of some instance in a network server resource. You can have an SPN for a Web service, for an SQL service, or for an SMTP service. You can also have multiple Web service instances on the same physical computer that has a unique SPN.

An SPN for SQL Server is composed of the following elements:

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.

For example, a typical SPN for a computer that is running SQL Server is:

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

The format of an SPN for a default instance and the format of an SPN for a named instance are not different. The port number is what ties the SPN to a particular instance.

When the SQL Server driver on a client uses integrated security to connect to SQL Server, the driver code on the client tries to resolve the fully qualified DNS of the computer that is running SQL Server by using the WinSock networking APIs. To perform this operation, the driver code calls the gethostbyname and gethostbyaddr WinSock APIs. Even if an IP address or host name is passed as the name of the computer that is running SQL Server, the SQL Server driver tries to resolve the fully qualified DNS of the computer if the computer is using integrated security.

When the SQL Server driver on the client resolves the fully qualified DNS of the computer that is running SQL Server, the corresponding DNS is used to form the SPN for this computer. Therefore, any issues pertaining to how the IP address or host name is resolved to the fully qualified DNS by WinSock may cause the SQL Server driver to create an invalid SPN for the computer that is running SQL Server.

For example, the invalid SPNs that the client-side SQL Server driver can form as resolved fully qualified DNS are:

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

When the SQL Server driver forms an SPN that is not valid, authentication still works because the SSPI interface tries to look up the SPN in the Active Directory directory service, and it does not find the SPN. If the SSPI interface does not find the SPN, Kerberos authentication is not performed. At that point, the SSPI layer switches to an NTLM authentication mode and the logon uses NTLM authentication and typically succeeds. If the SQL Server driver forms an SPN that is valid but is not assigned to the appropriate container, it tries to use the SPN but cannot, causing a "Cannot generate SSPI context" error message. If the SQL Server startup account is a local system account, the appropriate container is the computer name. For any other account, the appropriate container is the SQL Server startup account. Because authentication will try to use the first SPN that it finds, make sure that there are no SPNs assigned to inappropriate containers. In other words, each SPN must be assigned to one and only one container.

The key factor that makes Kerberos authentication successful is the valid DNS functionality on the network. You can verify this functionality on the client and the server by using the Ping command-line utility. On the client computer, run the following command to obtain the IP address of the server that is running SQL Server (where the name of the computer that is running SQL Server is SQLServer1):

ping sqlserver1

To see if the Ping command-line utility resolves the fully qualified DNS of SQLServer1, run the following command:

ping -a IPAddress

For example:

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:\>

When the command ping -a IPAddress resolves to the correct fully qualified DNS of the computer that is running SQL Server, the client side resolution is also successful.

SQL Server Service Principle Name creation

This is one of the critical parts of Kerberos and SQL Server interaction. With SQL Server, you can run the SQL Server service under one of the following: a LocalSystem account, a local user account, or a domain user account. When the SQL Server service instance starts, it tries to register its own SPN in Active Directory by using the DsWriteAccountSpn API call. If the call is not successful, the following warning is logged in Event Viewer:Source: MSSQLServer EventID: 19011 Description: SuperSocket info: (SpnRegister) : Error 8344. For more information about the DsWriteAccountSpn function, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ad/ad/dswriteaccountspn.asp

Simplified explanation

If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local account do not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up for the computer that is running SQL Server. If you test using a domain administrator account as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN are present.

Because you might not use a domain administrator account to run the SQL Server service (to prevent security risk), the computer that is running SQL Server cannot create its own SPN. Therefore, you must manually create an SPN for your computer that is running SQL Server if you want to use Kerberos when you connect to a computer that is running SQL Server. This is true if you are running SQL Server under a domain user account or under a local user account. The SPN you create must be assigned to the service account of the SQL Server service on that particular computer. The SPN cannot be assigned to the computer container unless the computer that is running SQL Server starts with local system. There must be one and only one SPN, and it must be assigned to the appropriate container. Typically, this is the current SQL Server service account. However, this is the computer account with local system.

Verify the domain

Verify that the domain that you log on to can communicate with the domain to which the computer that is running SQL Server belongs. There must also be proper name resolution in the domain.

If your logon domain is the same domain that the computer that is running SQL Server belongs to, use Windows authentication to log on to SQL Server. If authentication fails, there is a Windows account or domain account problem that you must address. Contact your security administrator or network administrator to verify the Windows account or domain account for appropriate permissions.
If your logon domain is different from the domain of the computer that is running SQL Server, check the trust relationship between the domains.
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.
Use the Account is Trusted for Delegation option in Active Directory Users and Computers when you start SQL Server.
Use the Manipulate Service Principal Names for Accounts (SetSPN.exe) utility in the Windows 2000 Resource Kit. Windows 2000 domain administrator accounts or Windows 2003 domain administrator accounts can use the utility to control the SPN that is assigned to a service and an account. In the case of 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 whenever 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 additional information, see the "Security Account Delegation" topic in SQL Server 2000 Books Online. To do so, visit the following Microsoft Web site:
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_2gmm.asp
For more information about Windows 2000 Resource Kits, visit the following Microsoft Web site:
http://www.microsoft.com/resources/documentation/windows/2000/server/reskit/en-us/default.asp
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
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 to a specific port

Verify the server environment

Check some basic settings on the computer where SQL Server is installed:

Kerberos is not supported on Windows 2000-based computers that are running Windows Clustering unless you have applied Service Pack 3 (or later) to Windows 2000. Therefore any attempt to use SSPI authentication on a clustered instance of SQL Server might not succeed. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
235529 Kerberos support on Windows 2000-based server clusters
Verify that the server is running Windows 2000 Service Pack 1 (SP1). For more information about Kerberos 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
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 SQL Server computer
Verify if 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://msdn.microsoft.com/library/en-us/instsql/in_overview_6k1f.asp

Verify the client environment

Verify the following on the client:

Make sure that the NTLM Security Support Provider is correctly installed 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"
Determine if you are using cached credentials. If you are logged on to the client with 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 if 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
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.
SSPI uses a file named Security.dll. If any other application installs a file with 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
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

The Client Network Utility (CNU) is delivered together with Microsoft Data Access Components (MDAC) and it is used to configure connectivity to computers that are running SQL Server. You can use the MDAC Cliconfg.exe CNU utility to configure connectivity:

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.
Check the Alias tab in the CNU to verify if an alias has been defined for the server that you are trying to connect. If a server alias has been 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 if the behavior changes.
If the alias server is not defined on CNU, add the alias for the server that you are connecting to. When you perform this task, you are also explicitly defining the protocol and optionally defining the IP address and the port.

Information to collect to open a Microsoft Product Support (PSS) case

If you cannot obtain the cause of the problem by using the troubleshooting steps in this article, collect the following information and open a Microsoft Product Support (PSS) case:

For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

Generate a sqldiag report from SQL Server. For more information, see the "sqldiag Utility" topic in SQL Server Books Online.
Capture a screenshot of the error on the client.
On the node that cannot connect to SQL Server, type the following command from the command prompt:
net start > started.txt
This command generates a file named Started.txt in the directory where you run the command.
Save the values for the registry key under the following registry key on your client computer:
HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER\CLIENT\CONNECTTO
In a clustered environment, get the value of following registry key for each node of the cluster:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\LSA\LMCompatibilityLevel
In a clustered environment, see if the following registry key exists on each cluster server node:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NTLMSsp
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.
Capture the results if you ping the computer name (or the SQL Network Name on a cluster) from the client.
Save the name of the user accounts you use to start each one of the SQL Server services (MSSQLServer, SQLServerAgent, MSSearch).
The support professional must know whether SQL Server is configured for Mixed Authentication or Windows Only Authentication.
See if you can connect to the computer that is running SQL Server from the same client by using SQL Server Authentication.
See if you can connect by using Named Pipes protocol.

How to manually set up a Service Principle Name for SQL Server

For more information about how to manually set up a Service Principle Name for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

319723 How to use Kerberos authentication in SQL Server

The Security Support Provider Interface (SSPI) is the interface to Microsoft Windows NT security that is used for Kerberos authentication, and supports the authentication scheme of the NTLM Security Support Provider. Authentication occurs at the operating system level when you log on to a Windows domain. Kerberos authentication is only available on Windows 2000-based computers that have Kerberos enabled and that are using Active Directory.

SSPI is only used for TCP/IP connections that are made by using Windows Authentication. (Windows Authentication is also known as Trusted Connections or Integrated Security.) SSPI is not used by Named Pipes or multi-protocol connections. Therefore, you can avoid the problem by configuring your clients to connect from a protocol other than TCP/IP.

When a SQL Server client tries to use integrated security over TCP/IP sockets to a remote computer that is running SQL Server, the SQL Server client network library uses the SSPI API to perform security delegation. The SQL Server network client (Dbnetlib.dll) makes a call to the AcquireCredentialsHandle function and passes in "negotiate" for the pszPackage parameter. This notifies the underlying security provider to perform negotiate delegation. In this context, negotiate means to try either Kerberos or NTLM authentication on Windows-based computers. In other words, Windows use Kerberos delegation if the destination computer that is running SQL Server has an associated, correctly configured SPN. Otherwise, Windows use NTLM delegation.

Note Verify that you are not using an account named "SYSTEM" to start any of the SQL Server services (MSSQLServer, SQLServerAgent, MSSearch). The keyword SYSTEM may cause conflicts with the Key Distribution Center (KDC).