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):
To see if the Ping command-line utility resolves the
fully qualified DNS of SQLServer1, run the following command:
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:
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:For more information about Windows 2000 Resource Kits, visit the
following Microsoft Web site:
- 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:
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:
- 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: 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).