Topics from the Microsoft SQL Server Protocols team - Netlibs, TDS, SQL Browser, etc.
Users sometime see the “Cannot Generate SSPI Context” error message. A very good source for troubleshooting the error is http://support.microsoft.com/default.aspx?scid=kb;en-us;811889. You can also find good information at Using Kerberos with SQL Server.
Here, I talk about one extreme situation: SQL server was running under Local System and was shutdown accidentally. The user then decides to run SQL server under a different account, e.g local account, domain account etc., for whatever reasons. Then he/she hit this “Cannot Generate SSPI Context” error when the client tries to connect the server. Keep in mind this only happens when TCP is enabled for the SQL server and is used by the client to connect the server.
What happened here is: When SQL server ran under Local System, it had successfully registered the Service Principle Name (SPN) for the service. The SPN is kept in the Active Directory and should be de-registered when the server is shutdown. Due to the accidental shutdown, SQL server failed to de-register the SPN. When the client connects to the server using TCP, it can find the SPN in the Active Directory and Kerberos will be used to perform the security delegation. However, the new account is not the correct container of the SPN, and Kerberos will fail.
When this happens, some people may choose to reinstall SQL Server or even the whole OS. They may be frustrated by the fact that the problem is still there if local or domain account is again chosen as the service account. The SPN in the Active Directory won’t go away even if you reinstall the OS.
Setspn.exe can be used to register/de-register SPNs. One can register the same SPN for the same container more than one time. The registration beyond the first registration may not do anything. One de-registration will remove the SPN from Active Directory totally. Because of this, the easiest first step to troubleshoot “Cannot Generate SSPI Context” is to run SQL server under Local System account and gracefully shut it down. You can then change your service account to whatever you want. SPN will not be registered and clients will fallback to use NTLM.
Also note that, if you made any change related to SPN or service account on the server, the cached information on the clients may need a couple of minutes to go away. You may see some inconsistent information during this period. Just wait several minutes in this case.
Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
i have the same problem and in my case the probelm was the antivirus (Trend Micro - OfficeScan). I desable the antivirus firewall and it works perfectly.
Sorry for my english.
In this post, I focus on how NTLM and Kerberos are applied when connecting to SQL Server 2005 and try
Incorrect DNS can lead to various network connectivity issues. In this post, I explain how it affects
how come i resolve thiz Problem in SQL 2000 ON XP MACHINES......
Description..........Can't generate SSPI CONTEX......
Got the same message with my local server when logging in with my windows account(admin on the machine). I finally simply logged in as "sa", then exited cleanly. It did it. Everything is fine now.
//Create Procedure for searching data's
create proc batchShip_Search(@bId int,@dtFrom datetime,@dtTo datetime)
if(@bId=' ' and @dtFrom=' ' and @dtTo!=' ') begin
select bId,custName,dt from batchShip1 where dt<=@dtTo order by dt end
if(@bId=' ' and @dtFrom!=' ' and @dtTo=' ') begin
select bId,custName,dt from batchShip1 where dt>=@dtFrom order by dt end
if(@bId=' ' and @dtFrom!=' ' and @dtTo!=' ') begin
select bId,custName,dt from batchShip1 where dt>=@dtFrom and dt<=@dtTo order by dt end
if(@bId!=' ' and @dtFrom=' ' and @dtTo=' ') begin
select bId,custName,dt from batchShip1 where bId=@bId order by bId end
if(@bId!=' ' and @dtFrom=' ' and @dtTo!=' ') begin
select bId,custName,dt from batchShip1 where bId=@bId and dt<=@dtTo order by bId end
if(@bId!=' ' and @dtFrom!=' ' and @dtTo=' ') begin
select bId,custName,dt from batchShip1 where bId=@bId and dt>=@dtFrom order by dt end
if(@bId!=' ' and @dtFrom!=' ' and @dtTo!=' ') begin
select bId,custName,dt from batchShip1 where bId=@bId and dt>=@dtFrom and dt<=@dtTo order by dt end
//Given data's which is searching
hi i have the same problem but i did everithing that read in google, other idea thanks
I had an accidental shut down of my system.
(my sql server and client are on the same machine).
Due to which i m unable to connect to the sql server.
it is showing me the "cannot generate sspi context" message.
Can you check my another post? Your issue could be DNS related.