Troubleshooting “Cannot Generate SSPI Context” Errors
It all started once we migrated to Active
started seeing intermittent "Cannot
Generate SSPI Context..." errors on
some of our SQL Servers, both SQL 2000 and SQL 7.0. As we all know, Active Directory is much
more complex when compared to a traditional NT 4.0 domain. For this reason, I'm glad I left LAN
Administration several years before Active Directory. As a DBA I do not need to know how to setup an AD
domain or a DNS server, but I still need to know how Kerberos, Service Principle
Names, and hostnames are used to perform integrated authentication to a SQL
Performing Integrated Security to a SQL Server
To successfully perform integrated security to a SQL
Server, the client user's Windows security token must be delegated from the
client machine to the SQL Server machine. There are three possible ways that the
SQL Server driver can choose to delegate a user's security token from one
machine to another:
1. NTLM over Named Pipes (not using
2. NTLM over TCP-IP sockets via
3. Kerberos over TCP-IP sockets via
This article is concerned with the third method.
SSPI or "Security Support Provider Interface” is a
set of Windows APIs that allows delegation and mutual authentication over any
generic data transport (such as TCP-IP sockets).
SSPI allows a Windows computer to securely delegate a
user’s security token from one machine to another over any transport that can
transmit raw bytes of data from the client and server.
You'll notice that NTLM over Named Pipes does not use
SSPI. Switching to Named Pipes is
one workaround to "Cannot Generate SSPI Context.. " errors.
The "Cannot generate SSPI context" error is generated
when SSPI chooses to use Kerberos to delegate over SSPI, and for some reason
Kerberos cannot complete the operations needed to delegate the user’s security
token to the target SQL Server.
With Kerberos, there exists the concept of an SPN or
Service Principle Name. Think of an SPN as a domain/forest unique identifier of
some instance of a network server resource. You can have an SPN for a web
service, a SQL service, an SMTP service, etc... You can also have multiple web
service instances on the same physical computer; each will have its own unique
An SPN for SQL Server is composed of three parts:
ServiceClass, Host, and Port. The ServiceClass is always MSSQLSvc for SQL
Server. The Host is the fully qualified domain name (FQDN) of the SQL Server.
The Port is the port number that the service is listening on. For example, here
is a typical SPN for a SQL Server:
When the SQL Server driver attempts to connect to a
SQL Server using integrated security, the driver code on the client machine
attempts to resolve the FQDN of the SQL Server using the WinSock networking
APIs. The driver code calls gethostbyname and gethostbyaddr
WinSock APIs to do this. Whatever the client resolves as the FQDN of the SQL
Server, via WinSock, is then used to form the SPN for the SQL
In order to use Kerberos over TCP-IP sockets via
SSPI, the SPN for SQL Server must be resolved correctly. Since SPN's require clients to resolve
the FQDN of the SQL Server, the DNS settings for both the server and client
should be verified. Use
NSLOOKUP on the client machine to verify this.
You may be wondering how the SPN gets created in the
first place; after all, in most cases you did not explicitly create it. Well, it depends on your choice of
When SQL Server starts up, it tries to register an
SPN for the service. If the service is started using the Local System account,
most of the time the SPN gets created successfully. However if SQL Server is started using a
local account or a domain account then the creation of the SPN fails (unless the
domain account is a domain admin).
You can also manually set an SPN using the SETSPN utility.
In Kerberos authentication, the service needs to
create an SPN, and the client has to build an SPN which exists to use Kerberos
authentication. In the absence of either of these, delegation will default to
NTLM, which does not use the SPN.
In addition to the DNS name, the SPN must be
correct. You can verify this by
running the Windows 2000 Resource Kit utility SETSPN with -L
SETSPN -L Myserver
You will notice if you do not have an SPN, the
delegation defaults to NTLM.
Another workaround is to delete the SPN using the Windows 2000 Resource
Kit utility SETSPN with a -D parameter.
What Time is it?
One last issue I've seen which causes "Cannot Generate SSPI Context..." errors is if the time on the SQL Server
is different from the AD domain controllers. Keeping time in sync with AD domain
controllers is important since Kerberos generate session tickets which have
Start, End, and Renewal times (see KLIST and KERBTRAY.EXE Windows
2000 Resource Kit utilities to view current tickets).
I noticed this issue when setting the date back to do
some testing on a QA server on a date-driven application. I set the date back several days and got
"Cannot Generate SSPI Context..." errors.
Once I reset the date to the correct date, those errors where fixed. I believe that if the time is off
more than 15 minutes this can cause authentication issues. I have also noticed an issue when
running third party time services (instead of running Windows Time Service,
which is included with Windows 2000).
For some reason, as soon as I shutdown the third party time service we
were running and enabled Windows Time Service instead, all of the annoying and
intermittent SSPI Context errors were fixed!
I've only seen "Cannot
Generate SSPI Context..." errors when using NT authentication over TCP/IP
sockets in an Active Directory doman
Most servers do not have
SPN's for SQL Server and are not needed, so you can safely delete the
settings on server and client
Shutdown any third
party time services and enable Windows Time Service
Run SETSPN -L
<ServerName> from the client which is experiencing the
SPN uses the DNS name of
the server, If the DNS name is incorrect verify issue is not w/ client by
running IPCONFIG /FLUSHDNS or restart client machine
Re-run SETSPN or
verify DNS name of server from client machine
If DNS name is still
incorrect contact your networking group
Removing the SPN entirely
should resolve the issue -- Run SETSPN -D <SPN>