Board index MS SQL Server

Cannot generate SSPI Context - reg key won't fix

Cannot generate SSPI Context - reg key won't fix

Post by Josh Jone » Fri, 23 Mar 2001 23:56:40



We're running a largely Windows 2000 network with SQL Server 7.0, with SQL
authentication. When we try to connect remotely (via an ISP), we get an
error that says "Cannot generate SSPI Context". Microsoft says a registry
key will fix it, and it does not. We've tried altering the connection string
to say PersistSecurityInfo=FALSE, and it doesn't work. Does anyone have any
ideas how to do this? Certainly SQL can't restrict you to merely connecting
via a LAN.

Here's our connection string:

Provider=sqloledb;
Network Library=dbmssocn;
Data Source=[our server's ip address];
Initial Catalog=[our database];
User ID=[our login];
Password=[our password];

The other property of our setup is MS Proxy Server, with Surrogate Sockets
installed to forward the requests to the SQL Server. The IP we type in is
our external IP address. However we know this works because if we dial out
from an internal machine, we can connect through the proxy but we can't
connect to the SQL server unless the LAN cable is plugged in. If it is
unplugged, we get the SSPI error. We have been able to unplug the cable
after the software starts though.

 
 
 
Top

1. FIX: Cannot Generate SSPI Context

Hi,

From searches through various SQLServer newsgroups it would appear many,
many people have had account delegation problems with Windows2000. The
common symptom is that you get "Cannot Generate SSPI Context" when trying to
connect from a client to a server using Integrated Security over TCP/IP. A
workaround is to use Named Pipes.

I have encoutered this problem so I would like to present what I believe is
the solution (certainly worked for all my servers).
It appears to arise when the MSSQLServer service runs under a domain
account. That domain account must have permissions in the 2000 domain to
create SPN's (Service Principal Name's). If  the MSSqlserver service runs
under the LocalSystem account everything is fine, also if you elevate the
permissions of the Domain account to Domain Admin (or whatever else allows
it to create SPN's in Active Directory) then that will also work. It
typically manifests itself if you change the name/IP of the SQLServer or you
change the Service Account from LocalSystem to a domain account.

To remedy the problem either use the LocalSytem account, Elevate the domain
permissions of the account (only recommended for testing of course) or use
the SETSPN utility which I believe comes with Windows2000 Resource Kit and
follow the instructions eg.:

SETSPN -A MSSQLSvc/MySqlserver.mydomain.com  MyDomainAccount

Hopefully, someone finds this information accurate and of use.

--
Nick Hall
Senior Database Administrator
www.figleaves.com/nick

Collect your free 10 voucher from the URL above.

2. Migrating Sybase to MS Sql Server

3. SQL2K Dev Ed on XP - cannot generate SSPI context

4. chang value list base on.....

5. cannot generate sspi context

6. sco open server and informix se

7. Cannot Generate SSPI Context

8. Oracle Financial Client/Server Development - SF east bay, CA (Rec)

9. Error: "Cannot generate SSPI context"

10. Cannot generate SSPI context error.

11. Cannot generate SSPI context ": Perhaps a solution!

12. Help! Cannot generate SSPI context *PROBLEM*

13. Cannot Generate SSPI Context


All times are UTC
Board index