Cannot generate SSPI context.

Question

Cannot generate SSPI context.

Febry

SSC Enthusiast

Points: 102
More actions
January 30, 2007 at 8:53 pm

#61098

Hai,..
I'm having a trouble with my SQL server 2000. I use Windows 2000 advance server SP4 and SQL Server 2000 sp4.
Sometimes i recieved "Cannot ganerate SSPI context" when i open enterprise manager and trying to connect to (local) instance of SQL Server.
If i restart the OS, it works just fine, but after a while it will happend again (the error).
Some website said that it's because of the domain.
At first, i use DNS from my ISP to connect the serverto the internet:
202.155.84.188
202.155.84.189
And i use IP:
192.168.7.12
subnet mask:
255.255.255.0
And default gateway:
192.168.7.1
I use the gateway to connect to the internet.
So my Sql server computer is connected to a gateway, and other computer that using the sql also connected to the gateway.
I've tried the tips from the kb/811889 article, except for the Active directory
because i didn't use Active Directory, and i use 127.0.0.1.
Now, i've change my setting (IP) to:
IP = 192.168.7.1
Subnet mask = 255.255.255.0 (so other computer on my network can access this server)
DNS = 127.0.0.1
But recently, i tried something and i got one step further on this issue.
By the time i cannot connect / open the Enterprise Manager from local (i open the EM from
the server itself), all of the client CAN / able access the database from remote.
And if i change the setting in the Client Network Utility, the error message that
i got also change.
If i use TCP/IP, the error is "Cannot generate SSPI context."
If i use the Named Pipes, the error is about cannot Connect()).
I know that EM is considered as client application by the SQL Server (so it must connect
using client setting), but what happen to Shared Memory? Because i access the EM from the
server itself not from remote.
Is there someone that can help me???
Thx,
rabbi

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

EdVassie SSC Guru Points: 60472 More actions · Answer 1

The error message you are getting means you need a Service Principal Name (SPN). Google SETSPN and SQL to see the syntax, and persuade one of your Infrastructure people to run the SETSPN for you.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

i have seen this error when a windows user account is locked out and the user is trying to connect to the sql server. once the account is unlocked he/she will be able to login successfully.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 3

It's definitely an AD security error. Google for it and you should find a couple solutions that hopefully will match up with yours.

Jeet SSCommitted Points: 1536 More actions · Answer 4

The only time we had this error was when the mechnasim that keeps the times on all the servers in sync went crazy and because the time on the domain server was out by more than a certain threshold on the other servers it would not setup the security context.

Thanks Jeet

ken ashcroft-394321 SSC Journeyman Points: 83 More actions · Answer 5

I solved this problem in the past by toggling between TCP and named pipes. I found that for me the issue had to do with Windows XP and being on a domain. Sometimes it seems that XP would trash its authentication (especially overnight) and would require either a reboot or the steps above to fix it. This problem can also be presented when trying to connect to network drives, etc, anything using AD authentication.

- Ken

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

had this problen quite frequently lately (ughh...)

First thing you need to do is download the setspn utility (link below):

http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/setspn-o.asp

Install it on your computer (or server). Next you'll need a Domain Admin account to use (unless you are a Domain Admin you cannot use setspn for this type of AD updates). Open up a DOS command window change directory to C:\Program Files\Resource Kit (the setspn installation default directory).

Then enter the following comand:

setspn -L servername

Your results may look something like this:

MSSQLSvc/servername.xxx.organization.org:1433

HOST/servername

HOST/servername.xxx.organization.org

Next enter the following commands:

setspn -D MSSQLSvc/servername.xxx.organization.org servername

setspn -D HOST/servername servername

setspn -D HOST/servername.xxx.organization.org servername

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

If it was an SPN problem, though, this should occur all the time. I'd start by looking at time synchronization. Especially since the first thing a system does is check in with a domain controller and as it does so it synchronizes its time. Kerberos, by default, only accepts a time skew of 5 minutes. Get outside of this window and you can get this error as well. At first a system will synchronize time every 45 minutes. However, after three consecutive attempts, the synchronization window will jump to 8 hours. I have seen systems which have gotten out of time synch in this short a window. If time synchronization is the issue, there are ways to manually set how often to synchronize the system.

Also, are there any relevant errors in the operating system's security and system event logs on your SQL Server?

K. Brian Kelley
@kbriankelley

Minaz Amin SSChampion Points: 11052 More actions · Answer 8

Hi,

This link have more information on the error you receive :

http://support.microsoft.com/kb/811889

Minaz Amin

"More Green More Oxygen !! Plant a tree today"

neotokyo SSCertifiable Points: 7239 More actions · Answer 9

found this post on sqlmag. might be a bit of an overload,

but it does have alot of useful information in it.

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=84680&enterthread=y

_________________________

Febry SSC Enthusiast Points: 102 More actions · Answer 10

Hai,....

Thanks alot you guys for the help!

I already follow the advices that you guys send and now the problem is gone.

I toggling between named pipes and the TCP/IP, install the update, and also "tuning" the SPN.

And for "Brian Kelley",.... i think there's also something wrong with Windows. Because from one of the articles that i found from MSDN said that sometimes Windows doesn't update the data of the SPN. And they said that it could rise this kind of problem as well. Because when the user try to access something that must use the data on from SPN and it has been "release" or have to be update, the windows didn't update it yet.

Well,... now my sql server is working fine. Thanks alot you guys......

Febry

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 11

Thanks for the update.

I've seen this in a few places, usually when we've changed service accounts. SETSPN has worked at times and not others, so I think this is a general error that could have a few issues.