“Cannot Generate SSPI Context” error message, more comments for SQL Server

“Cannot Generate SSPI Context” error message, more comments for SQL Server

Rate This
  • Comments 32

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

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • I've been dealing with Kerberos delegation setup with respect to
    Microsoft's CRM 3.0 product and while...
  • The “Cannot generate SSPI context” issue is described by http://support.microsoft.com/?id=811889 in general....
  • 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.
    please help.

    thanks
  • Key things I would do is download the setspn tool from link above.  Insure your SQL Server service is started.  Next try to delete all the SPNs you see for this instance of SQL using the setspn tool (read KB article 811889 to determine how to do this).  If you cannot delete the SPNs, then the SPNs are assigned to some domain user account and you need a domain administrator to delete it.  Delete all SPNs for the SQL Server instance, then stop and restart SQL.  Next, use the setspn tool to see if the SPN is re-created.  If the SPN is recreated, then everything should work fine at this point.  If the SPN is not created at this point, you will need to contact the domain admin and have him or her create the SPN under the account that the SQL Server service is running under.



  • I have a saparate test machine to test the new
    updation for my programmes.Therefore i have the client and server on the same machine and it was working fine till yesterday.Suddenly i got the above message "Cannot generate SSPI Context" error message when i expanded my local server.
  • Hi, Mahesh

       Can you describe more specifically about your problem?

    Try the solution that Matt Neerincx suggested above.

    Thanks!
    Ming.
  • Hy,

    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.

    PS.

    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......

    Error message......0x80004005.

    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)

    as begin

    if(@bId=' ' and @dtFrom=' ' and @dtTo!=' ') begin

    select bId,custName,dt from batchShip1 where dt<=@dtTo order by dt  end

    else

    if(@bId=' ' and @dtFrom!=' ' and @dtTo=' ') begin

    select bId,custName,dt from batchShip1 where dt>=@dtFrom order by dt end

    else

    if(@bId=' ' and @dtFrom!=' ' and @dtTo!=' ') begin

    select bId,custName,dt from batchShip1 where dt>=@dtFrom and dt<=@dtTo order by dt end

    else

    if(@bId!=' ' and @dtFrom=' ' and @dtTo=' ') begin

    select bId,custName,dt from batchShip1 where bId=@bId order by bId end

    else

    if(@bId!=' ' and @dtFrom=' ' and @dtTo!=' ') begin

    select bId,custName,dt from batchShip1 where bId=@bId and dt<=@dtTo order by bId end

    else

    if(@bId!=' ' and @dtFrom!=' ' and @dtTo=' ') begin

    select bId,custName,dt from batchShip1 where bId=@bId and dt>=@dtFrom order by dt end

    else

    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

    end

    //Given data's which is searching

    batchShip_Search '1','2007-12-01','2007-12-30'

  • 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.

    please help.

  • Can you check my another post? Your issue could be DNS related.

    http://blogs.msdn.com/sql_protocols/archive/2007/01/02/cannot-generate-sspi-context-error-message-poisoned-dns.aspx

Page 1 of 3 (32 items) 123