Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Troubleshooting Cannot Generate SSPI Context Errors

By Chad Miller, (first published: 2003/02/27)

Troubleshooting “Cannot Generate SSPI Context” Errors

It all started once we migrated to Active Directory I 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 Server.

 

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 SSPI)

2. NTLM over TCP-IP sockets via SSPI

3. Kerberos over TCP-IP sockets via SSPI

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

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:

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

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

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 service account.

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

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!

Additional Notes:

·        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 SPN

Troubleshooting Tips:

·         Verify date/time settings on server and client

·         Shutdown any third party time services and enable Windows Time Service

·         Verify DNS settings/name resolution

·         Run SETSPN -L <ServerName> from the client which is experiencing the error

·        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> <ServerName>

Total article views: 65194 | Views in the last 30 days: 53
 
Related Articles
FORUM

Client & Server Codepages error

Client & Server Codepages error

FORUM

Generic Service SQL Server Fulltext Failed to start service. Error: 1053

Generic Service SQL Server Fulltext Failed to start service. Error: 1053

FORUM

Client Certificates and Web Services in SSIS

Error with passing a client certificate to a web service

BLOG

Service pack Native Client, SQL XML4 and VSSWriter failed

One of my client accidentally executed a 32 bit service pack on a 64 bit SQL Server, the service P...

ARTICLE

Reporting Services 2005 101 using Web Client

Continuing with his series on different aspects of Reporting Services, Asif Sayed examines the next ...

Tags
administration    
configuring    
installation    
security    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones