Troubleshooting Cannot Generate SSPI Context Errors
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cmiller/cannotgeneratesspicontext.asp
-
Good topic, I've had to troubleshoot one of these before.
I'd like to add one more possible cause.
The SQL Server in Q was a test server on a physically seperate LAN.
The DOM Admins had called it "SLQ1".
They later renamed it to "SQL1", but renaming an object in AD is not foolproof nor as simpel as in NT, there are lots of places where it stays the same.
I'd trawled the KB and Google, I checked NTLM settings and timezones, no joy.
The clue lay in the Windows Event Log, lots of Kerberos ticket errors, and this pointed to duplicate object names on teh LAN. Renaming the server back to "SLQ1" fixed it.
The SID was the same for SLQ1 and SQL1 thus coudl not be resolved.
To rename the object they should have dropped it from AD, renamed it, changed the SID and then added back to AD.
Cheers
Shawn
-
Hi there
Well well well, blow me down if I didnt get the error yesterday! after thinking that will all the instals i have done I never hit it before.
Your article is good, but really needed some screen shots, and more step by step processes to resolve the issue(s).
In my case I was using a domain user account to run the sql server and sql-agent services. During install, i removed named pipes from the provider list for my listener. All worked fine for a while, even after some reboots, I applied SP3 and things went haywire with the SSPI error.
On loging into the servdr as the sqlserveradmin user account i created, the instance would start, but attempting to connection via windows authentication to the instance resulted in the SSPI context error. I changed it back to local system and all worked fine. The agent service was also giving me an error:
SQL Agent Error:
[165] ODBC Error: 0, Cannot generate SSPI context [SQLSTATE HY000]
I download setspn from http://www.petri.co.il/download_free_reskit_tools.htm
and run the following:
C:\Program Files\Resource Kit>setspn -L royntsq02
Registered ServicePrincipalNames for CN=ROYNTSQ02,CN=Computers,DC=zzzzzz,DC=xxxxx,DC=wa,DC=xxx,DC=au:
MSSQLSvc/xxxxxxxx.zzzzz.xxxxxx.wa.xxx.au:2433
HOST/ROYNTSQ02
all looking good, but found the nslookup failed:
C:\Program Files\Resource Kit>nslookup
DNS request timed out.
timeout was 2 seconds.
*** Can't find server name for address 163.232.6.19: Timed out
DNS request timed out.
timeout was 2 seconds.
*** Can't find server name for address 163.232.6.22: Timed out
*** Default servers are not available
Default Server: UnKnown
not good. The system admins resolved the issue, only to get the error "there is a time difference between the client and the server" when changing the service account for sql server back to the sqlserveradmin user.
Again they resynced, I rebooted the box and all works a treat over TCP/IP and the sqlserveradmin user account.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Its really a nice article and after reading the article I could figure out my problem and the reason of the problem.
Pankaj Suri.
Pankaj Suri. -
Interesting article; however, troubleshooting this error message can be quite complex. I've worked with Microsoft since 4/12/02 on a 3-tier application that was having this problem intermittently. Low and behold, they released a private hotfix on 5/22/03 and it's scheduled to be part of SP4. It may not be part of SP4 though, because I noticed that the download is in the SP5 directory.
-
I just wanted to let people know that this message will pop up if you don't have the Client for Microsoft Networks installed.
-
In my environment, people are getting this error for a different reason. Some are using Terminal Services and reconnecting to a disconnected session after changing their network password. The open program such as Query Analyzer generates the error. The solution is logging out of the terminal services connection (instead of disconnecting) and reconnecting again.
-
I started getting this error right out of the blue, I followed the instructions given and bingo it worked.
-
Hi
I had intermittent 'Cannot Generate SSPI Context Errors' from an installation of Reporting Services 2000 SP1, even though SQL Server itself seemed to be fine. I could do all the usual stuff with SQL server directly but I could not go to either the \\servername\reports or \\servername\reportserver web pages. Nor could a .net application that generated graphical output using the report server web service run successfully. Things would work fine for days then have a couple of hours (or days) off then magically start working again. I read all the above posts and article which, while pointing me in the right direction, did not resolve the problem.
It seems to be a DNS synchronisation problem, and the only clue I could find was a netlogon error message (event ID 5783) in the system events log of \\server ....
"The session setup to the windows NT or windows 2000 Domain Controller \\controllerservername for the domian domainname is not responsive. The current RPC call from Netlogon on \\servername has been cancelled"
I'm not a network guy, but the network admins for my company manually synched \\server with another domain controller and the problem went away...... Hope this might help someone,as I'm always grateful to the people who post stuff that helps me......
Cheers.
-
Hello,
I have encountered similar error "Cannot Generate SSPI Context" it seemed from nowhere. I have read the article (and other also) and resources on microsoft site, but while trying to identify the problem in the - active directory/kerberos/name resolving - i found interesting effect. Read further.
We have SQL Server 2000 SP3 is running on the win2003 cluster, everything works fine (switching, recovery...). Authentication method is 'Mixed mode'. Users are working in the terminal with application, which uses domain user to connect to SQL Server (maybe its not very efficient, but we do not discuss that issue now).
However due to databse fragmentation the performance of application bacame very poor, many looks due to scaning indexes. So reindexing should help.
However firstly one of the fastest methods of somewhat improoving SQL Server speed is to use option "Use windows NT fibers". So I checked it and restarted server, during the night DB maintenence plan was run to reindex database tables, to further improove performance. In the morning the users started complaining that they cannot conect to the server through application. By the way, they mentioned that in the evening they also could not connect to the server from that time I have restarted server (after enabling NT fibres). Thus i turned off "Use windows NT fibers" option and restarted the server. Everything again works fine, no SSPI error
Can someone answer the question how NT fibers are related to all these things writen about SSPI, which is somewhat as "name resolving/kerberos/active directory services" error? Because from my experience the SSPI error is directly related to enabling the option "NT fibers". As far as i understand fibers are operating system mechanizm of optimizing its work. The kerberos protocol, which is used for authentication/ticket granting/etc is also operating system integrated, but it issues the commands to OS kernel, and only after that OS organizes the threads and fibers to plan the processor time. So if we would look at the win2003 OS as a simplified hierarchical structure, I would see the following picture:
I can not figure out why all these SSPI errors were generated?
P.S. Index defragmentation helped, DB is performing sufficiently, for now
-
Hi,
I've been experiencing this problem for a long time and this is the first article or reference I have found that addresses it. We have SQL Server 2000 sp3 running on Windows Server 2003 at a client's site, and a client/server application written in VB6 that accesses it, and we get this error intermittently. We've just been telling them to log off and log on again! I'll try the suggestion in this article.
Daniel
-
Chad,
thanks for the article, I as well have seen this error and everytime I try to research it all I find are documents from the perspective of Kerbos in general, or from the point of view of a Network administrator (which I am not). You elluded to part of the answer in your article and I have re-printed the quote below
"(see KLIST and KERBTRAY.EXE Windows 2000 Resource Kit utilities to view current tickets). "
My question is how do I know if a connection is comming in as kerbos or is demoting to NTLM. What was your perspective on using Klist or the KerbTray.exe applications to view Kerbos connections. How did you view NTLM conections? From this where there any tricks learned? Ofcourse I'm off to try myself, but I thought I would bring it up as a possible add in to the next revision of this Doc (if any). Ideally I would like to know how to check each of the three types of connections so that I might know definitely how my connection are occuring to the database.
The 3 types
1. NTLM over Named Pipes (not using SSPI)
2. NTLM over TCP-IP sockets via SSPI
3. Kerberos over TCP-IP sockets via SSPI
Thanks,
Mudluck
-
I'm also getting the SSPI Context error due to changing the system time on the server for test purposes. Is there really no way of doing this without getting an error? How do you simulate the passing of time when testing?
-
I was able to get around this issue by setting up an alias for the server giving me trouble in the Client Network Utility.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply