How to troubleshoot the "Cannot generate SSPI context" error message
Understanding the problem
- 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 NameThe 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 authentication over TCP/IP sockets with SSPI
The "Cannot generate SSPI context" error is generated when SSPI uses Kerberos authentication to delegate over TCP/IP and Kerberos authentication 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 uses NTLM or Kerberos authenticationKerberos authentication uses an identifier named "Service Principal Name" (SPN). Consider an SPN as a domain or forest unique identifier of some instance in a server resource. You can have an SPN for a web service, for a 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.
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 about 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 as follows:
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 prompt 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):
C:\>ping SQLSERVER1Pinging SQLSERVER1 [126.96.36.199] with 32 bytes of data: Reply from 188.8.131.52: bytes=32 time<10ms TTL=128Reply from 184.108.40.206: bytes=32 time<10ms TTL=128Reply from 220.127.116.11: bytes=32 time<10ms TTL=128Reply from 18.104.22.168: bytes=32 time<10ms TTL=128 Ping statistics for 22.214.171.124: 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 126.96.36.199 Pinging SQLSERVER1.northamerica.corp.mycompany.com [188.8.131.52] with 32 bytes of data: Reply from 184.108.40.206: bytes=32 time<10ms TTL=128Reply from 220.127.116.11: bytes=32 time<10ms TTL=128Reply from 18.104.22.168: bytes=32 time<10ms TTL=128Reply from 22.214.171.124: bytes=32 time<10ms TTL=128Ping statistics for 126.96.36.199: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0msC:\>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 Principal Name creationThis is one of the important parts of Kerberos authentication 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, go to the following Microsoft website:
Simplified explanationIf you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos authentication 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 by 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 authentication 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 the local system account. 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, but this is the computer account container with the local system account.
Resolving the problem
Verify the domainVerify that the domain to which you log on can communicate with the domain to which the computer that is running SQL Server belongs. There must also be correct name resolution in the domain.
- 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.
- If your logon domain differs 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.
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.
- 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:
- 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 DNS224196 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 instancesTo configure the SQL Server service to create SPNs dynamically, you must change the account's access control settings in the Active Directory directory service. You must grant the "Read servicePrincipalName" permission and the "Write servicePrincipalName" permission for the SQL Server service account.
Warning If you use the Active Directory Service Interfaces (ADSI) Edit snap-in, the LDP utility, or any other LDAP version 3 clients and you incorrectly change the attributes of Active Directory objects, you can cause serious problems. These problems may require you to reinstall Windows Server 2003, Microsoft Windows 2000 Server, Microsoft Exchange Server 2003, Microsoft Exchange 2000 Server, or both Windows and Exchange. We cannot guarantee that problems caused by incorrectly changing the attributes of Active Directory objects can be resolved. Change these attributes at your own risk.
Note To grant the appropriate permissions and user rights to the SQL Server startup account, you must be logged on as a domain administrator, or you must ask your domain administrator to do this task.
To configure the SQL Server service to create SPNs dynamically, follow these steps:
- Click Start, click Run, type Adsiedit.msc, and then click OK.
- In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName, and then click Properties.
- 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.
- In the CN= AccountName Properties dialog box, click the Security tab.
- On the Security tab, click Advanced.
- 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.
- Under Permission entries, click SELF, and then click Edit.
- In the Permission Entry dialog box, click the Properties tab.
- 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
- Click OK three times, and then exit the ADSI Edit snap-in.
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.
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:
- The Sqlcluster instance is active on Node A and registered the SQL SPN in domain controller A during start up..
- The Sqlcluster instance fails over to Node B when Node A is shutdown normally.
- The Sqlcluster instance deregistered its SPN from domain controller A during the shutdown process on Node A.
- The SPN is removed from domain controller A but the change has not yet been replicated to domain controller B.
- 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.
- 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.
Verify the server environmentCheck some basic settings on the computer where SQL Server is installed:
- 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
- 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
- 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
- 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:
Verify the client environmentVerify the following on the client:
- 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"
- 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
- 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 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
- 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 utilityThe 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 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.
- 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 ServerFor more information about how to manually set up a Service Principal Name for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
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 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, configured correctly 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).
Collect information to open a Microsoft Customer Support (CSS) case
For a complete list of Microsoft Customer Support telephone numbers and information about support costs, go to the following Microsoft website:
- Generate a sqldiag report from SQL Server. For more information, see the "sqldiag Utility" topic in SQL Server Books Online.
- Capture a screen shot of the error on the client.
- Open a command prompt on the node that cannot connect to SQL Server, and then type the following command:net start > started.txtThis command generates a file that is named Started.txt in the directory where you run the command.
- Save the values for the registry key under the following registry key on the client computer: HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER\CLIENT\CONNECTTO
- 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
- In a clustered environment, see whether 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 that 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 whether you can connect to the computer that is running SQL Server from the same client by using SQL Server Authentication.
- See whether you can connect by using Named Pipes protocol.
ReferencesFor more information about how Kerberos authentication and SSPI security works, click the following article numbers to view the articles in the Microsoft Knowledge Base:
Article ID: 811889 - Last Review: 07/11/2013 08:14:00 - Revision: 29.0
- kbsqlsetup kbhowtomaster kbhowto kbsmbportal KB811889