error: Cannot Generate SSPI Context

Last Post 30 Sep 2009 01:19 PM by brianmultilanguage. 8 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sqladmin
New Member
New Member

--
22 Mar 2007 11:31 AM
note: sspi errors are sometimes fixed (at least in 2005) simply by adjusting the service login
account to "local system" this has worked in many situations regarding connectivity issues
between sql server, and analysis services.

moving on... in other cases..
here is how to fix the SSPI Context error in 2 easy steps.


STEP 1:

you will need the Setspn utility from Microsoft, and you'll need
domain admin rights to run it.

where do you get the Setspn utility? you can download it from this
link:
http://www.microsoft.com/downloads/...laylang=en

or you can get it from the Windows 2003 Server SP1 CD.
it's called: Support Tools for SP1.exe

you could always google for the suptools.msi, or setspn.exe if
you need.

once you get the Support Tools installed either on your workstation,
or on the server it's self you can find the Setspn.exe utility in the
following location:

c:\program files\support tools

the Setspn utility can only be used from the Command Prompt.

STEP 2:

Go to: Start -> Run & type in CMD

type in:
cd program files\support tools

from here you will need to specify the domain user account that you are
using to connect from Management Studio, or Enterprise Manager

type in:
setspn -L MyDomain\MyUserName

now you should see the output which looks something like the following:

C:\Program Files\Support Tools>setspn -L MyDomain\MyUserName
Registered ServicePrincipleNames for CN=MyUserName Service Account, OU=Service Accounts,
DC=MyDomain,DC=com:
mssqlsvc/MyServer1.MyDomain.com:MyPort
mssqlsvc/MyServer2.MyDomain.com:MyPort
mssqlsvc/MyServer3.MyDomain.com:MyPort
mssqlsvc/MyServer4.MyDomain.com:Myport

From the results you should be able to see a list of servers who's SQL SPN has
been successfully registered to the DNS.
Note: not all your servers will be represented here, but what you are looking for
is the Server that you are getting the SSPI error on.

Do you see the Server that you are having
the SSPI Context error for?

If not... Just type in the following:

setspn -a mssqlsvc MyServer MyDomain\MyUserName

if this syntax does not work for you. try this:

setspn -A MSSQLSvc/MyServer.MyDomain.com:1433 MyAccount


once this is done check again to see if your Server is in the list
by running the following:

setspn -L MyDomain\MyUserName

now you should see your Server in there.

try going back to your Management Studio or Enterprise Manager and
connecting to the server.

Thats it.


the rest of the posts under this thread are there for more information
if you would like to understand more about how the SPN works.


sqladmin
New Member
New Member

--
22 Mar 2007 02:42 PM
if after manually registering the spn for the sql server service account you still receive the error...

1)Did you successfully register the SPN through Setspn.exe?
2)you still receive which error?0x2098? If so, that means you are using invalid account to register SPN, check your DC to verify this account.
3)Did you enable TCP/IP protocol and restart server?
4)which service account your sql server is running under? It should be admin account such as domain admin account or localsystem account.
5)Which OS you came across this problem, XP?

In a word, try setspn.exe( here is a useful link about setspn, http://technet2.microsoft.com/Windo...mfr=true), see if you can register SPN? if it report error 0x2098, it means the account is invalid, contact your domain administrator to check the account in DC.

- ming SQL Protocols

more information can be found at:
http://blogs.msdn.com/sql_protocols...79871.aspx
sqladmin
New Member
New Member

--
02 May 2007 11:04 AM
here is more information on SPN's, script examples, SPN tool, how SPN's work, why and all that other stuff.

Setspn Overview
http://207.46.196.114/windowsserver...x?mfr=true

Setspn Syntax Refernce
http://207.46.196.114/windowsserver...x?mfr=true

How to use Service Principle Names (SPN)
http://www.pluralsight.com/wiki/default.aspx/Keith.GuideBook/HowToUseServicePrincipalNames.html

How to Configure the Service Principal Name
http://technet2.microsoft.com/windowsserver/en/library/2bbd23c5-a01d-49bc-8b1c-6d309767c5e71033.mspx?mfr=true

Setspn.exe Examples as of March 2003
http://technet2.microsoft.com/windowsserver/en/library/2bbd23c5-a01d-49bc-8b1c-6d309767c5e71033.mspx?mfr=true


sqladmin
New Member
New Member

--
02 May 2007 11:26 AM
simply put by Microsoft:

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:
http://msdn2.microsoft.com/en-us/li...76056.aspx (http://msdn2.microsoft.com/en-us/li...76056.aspx)
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.
sqladmin
New Member
New Member

--
03 May 2007 03:58 AM
np
sqladmin
New Member
New Member

--
03 May 2007 06:31 AM
here is an article about sql protocols which explains this error: Cannot Generate SSPI Context.

“Cannot Generate SSPI Context” error message.

Users sometime see the “Cannot Generate SSPI Context” error message.
A very good source for troubleshooting the error is
http://support.microsoft.com/defaul...us;811889.
You can also find good information at Using Kerberos with SQL Server.

Here, I talk about one extreme situation: SQL server was running under
Local System and was shutdown accidentally. The user then decides to
run SQL server under a different account, e.g local account, domain
account etc., for whatever reasons. Then he/she hit this
“Cannot Generate SSPI Context” error when the client tries to connect
the server. Keep in mind this only happens when TCP is enabled for the
SQL server and is used by the client to connect the server.

What happened here is: When SQL server ran under Local System, it
had successfully registered the Service Principle Name (SPN) for the
service. The SPN is kept in the Active Directory and should be
de-registered when the server is shutdown. Due to the accidental
shutdown, SQL server failed to de-register the SPN. When the client
connects to the server using TCP, it can find the SPN in the
Active Directory and Kerberos will be used to perform the security
delegation. However, the new account is not the correct container
of the SPN, and Kerberos will fail.

When this happens, some people may choose to reinstall SQL Server
or even the whole OS. They may be frustrated by the fact that the
problem is still there if local or domain account is again chosen as
the service account. The SPN in the Active Directory won’t go away
even if you reinstall the OS.

Setspn.exe can be used to register/de-register SPNs. One can
register the same SPN for the same container more than one time.
The registration beyond the first registration may not do anything.
One de-registration will remove the SPN from Active Directory
totally. Because of this, the easiest first step to troubleshoot
“Cannot Generate SSPI Context” is to run SQL server under
Local System account and gracefully shut it down. You can then
change your service account to whatever you want. SPN will not
be registered and clients will fallback to use NTLM.

Also note that, if you made any change related to SPN or service
account on the server, the cached information on the clients may
need a couple of minutes to go away. You may see some
inconsistent information during this period. Just wait several
minutes in this case.

- Xinwei Hong, SQL Server Protocols

About SPN's:

Registering an SPN:

The SPN is essentially a mapping between a principal name and the
Windows account that started the server instance service. This is
needed because the client will use the server’s hostname and the
TCP/IP port to which it connects to compose an SPN. If the SPN
mapping has not been performed, then the Windows security layer
will be unable to determine the account associated with the SPN and
Kerberos authentication will not be used. In an attempt to facilitate this,
the SQL Server 2005 instance will automatically try to register the SPN
with the AD at startup if TCP/IP is enabled. The problem is, however,
that only a domain administrator or a Local System account has the
authority to register an SPN. Therefore, in the majority of cases where
the service is started under a normal account, SQL Server will be unable
to register the SPN for the instance. This will not prevent the instance
from starting bu
dashburn
New Member
New Member

--
03 Aug 2009 08:56 AM
Thanks for the great info. We had to open a case with MS to get our issue solved but your info put us within the 1 yard line . What we were missing was a way to query AD for all the SPN's it had. Here is the vb script that MS had us run to get that info...

http://www.microsoft.com/technet/sc...query.mspx

Copy everything in the grey into a text file called querySPN.vbs and save it to c:\
Then run C:\>cscript querySPN.vbs MSSQLSvc/ppmasql* > c:\querySPN_out.txt
Look for your server, login name and port in the text file. You may see that your SPN is registered to an account you weren't aware of.
Delete the incorrect entry(s) first and then register the correct one(s).
Example:
C:\> setspn -D MSSQLSvc/SERVERNAME.DOMAIN.com:1433 DOMAIN.com\BADACCOUNT
C:\> setspn -A MSSQLSvc/SERVERNAME.DOMAIN.com:1121 DOMAIN.com\GOODACCOUNT

We kept trying to register the GOODACCOUNT name and it looked like it worked but without the VB script we couldn't see that BADACCOUNT was already in play. Registering GOODACCOUNT didn't trump BADACCOUNT.
sqladmin
New Member
New Member

--
14 Aug 2009 10:08 AM


If an SPN already exists, then it must be deleted before it can be re-registered.
This is accomplished by a domain admin using the setspn -D command.

brianmultilanguage
New Member
New Member

--
30 Sep 2009 01:19 PM
So even though this error comes from a workstation, SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security, and not the SQL server, the error is actually fixed on the SQL server not the workstation?


Acceptable Use Policy
---