SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Troubleshooting Cannot Generate SSPI Context Errors


Troubleshooting Cannot Generate SSPI Context Errors

Author
Message
cmille19
cmille19
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1339 Visits: 726
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cmiller/cannotgeneratesspicontext.asp



gbn
gbn
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2439 Visits: 62
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



ckempste
ckempste
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3149 Visits: 1
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"
Pankaj suri
Pankaj suri
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 15
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.

barnacky
barnacky
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 1
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.



TheMike
TheMike
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 1
I just wanted to let people know that this message will pop up if you don't have the Client for Microsoft Networks installed.



jimk
jimk
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 1
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.



Norm-131787
Norm-131787
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 1

I started getting this error right out of the blue, I followed the instructions given and bingo it worked.


Neil Cowan-192141
Neil Cowan-192141
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 23

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.


Benediktas Laurinavicius
Benediktas Laurinavicius
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 1

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search