Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Troubleshooting Cannot Generate SSPI Context Errors Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2003 12:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cmiller/cannotgeneratesspicontext.asp


Post #10251
Posted Friday, February 28, 2003 6:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 27, 2010 3:05 PM
Points: 1,629, 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



Post #55059
Posted Wednesday, March 5, 2003 9:54 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, 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"
Post #55060
Posted Tuesday, April 29, 2003 8:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 5, 2009 12:48 PM
Points: 224, 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.
Post #55061
Posted Friday, May 23, 2003 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 11, 2003 12:00 AM
Points: 143, 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.




Post #55062
Posted Monday, October 20, 2003 5:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2003 12:00 AM
Points: 1, 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.




Post #55063
Posted Sunday, February 29, 2004 12:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 22, 2007 10:49 PM
Points: 76, 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.


Post #103189
Posted Wednesday, June 9, 2004 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 9, 2004 4:21 AM
Points: 1, Visits: 1

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

Post #120020
Posted Sunday, March 6, 2005 8:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 1, 2012 5:02 PM
Points: 1, 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.

 

Post #165940
Posted Friday, September 16, 2005 2:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 22, 2007 9:39 AM
Points: 3, 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

 

Post #220510
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse