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>