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


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


Comments (32)

  1. Brian Kelley says:

    I’ve been dealing with Kerberos delegation setup with respect to

    Microsoft’s CRM 3.0 product and while…

  2. The “Cannot generate SSPI context” issue is described by http://support.microsoft.com/?id=811889 in general….

  3. udit b halla says:

    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

  4. Matt Neerincx [MSFT] says:

    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.

  5. Mahesh Manik says:

    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.

  6. SQL Protocols says:

    Hi, Mahesh

       Can you describe more specifically about your problem?

    Try the solution that Matt Neerincx suggested above.

    Thanks!

    Ming.

  7. elf says:

    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.

  8. In this post, I focus on how NTLM and Kerberos are applied when connecting to SQL Server 2005 and try

  9. Incorrect DNS can lead to various network connectivity issues. In this post, I explain how it affects

  10. jamshad says:

    how come i resolve thiz Problem in SQL 2000 ON XP MACHINES……

    Error message……0x80004005.

    Description……….Can’t  generate SSPI CONTEX……

  11. RichardB says:

    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.

  12. Billy says:

    //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′

  13. taz_ues@hotmail.com says:

    hi i have the same problem but i did everithing that read in google, other idea thanks

  14. atul says:

    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.

  15. Zohaib says:

    Sql works fine on other windows enviroment but it sometimes gives the error on only WinXp machines on my Network. Kindly  

  16. Xinwei Hong says:

    Please post a question on our forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

    Use the guideline at:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

    Then, we can find out what’s the root cause.

  17. Chris says:

    I’ve been trying to debug this error for days now on my test servers. I have IIS on a webserver, then SQL Server 2005 on a second server. They are not part of a domain and are stand alone servers as these is usual for a web application. Both servers are 2k3.

    I have duplicate users on both servers. The servers are directly connected to each other with no AD running. Currently I am testing with them directly connected, but will be placing a firewall between them on the production set up.

    Now, if both servers are part of the same domain, this works fine, but who wants a web server in your domain?

    As it is extremely unsecure to house a sql server on your web server and not firewall off the sql server, one would think that this type of set up would have been made a little more simple to accomplish.

    I’m basically beyond frustrated with this.

    I am no longer sure where to start over at. I’d reformat and reinstall both servers if I’d think that that would help, but I don’t because it’s never worked. LOL

  18. SQL Protocols says:

    Can you please check my other blog and try the step? http://blogs.msdn.com/sql_protocols/archive/2007/05/12/connecting-to-sql-server-from-a-workgroup-using-windows-authentication.aspx

    If you have firewall between two machines, you have to open port for NP(SMB, file sharing) and TCP (tcp port, for SQL Auth).

  19. Jeremie says:

    Thanks for the post. It’s just to bad this wasn’t on the Microsoft posting I found first.

  20. PJ says:

    I dont want to beat a dead horse with this issue, but I just cant seem to find the answer….

    Environment:

    Server 2003 SP2 R2 – simple Office 2007 setup with SQL server 2005

    Clients – Winxp SP2

    When I logon to windows from a client pc – all is well can connect to SQL on the server. 10 to 15 minutes later the connection is dropped, and I get the "Cannot generate SSPI context" (Outlook) or "SQL server does not exist, service has stopped, or Access denied" (Accounting)

    I have ensured that SQL service is running and set to auto on server and client pc’s

    net config server /AUTODISCONNECT:-1

    on server and pc’s

    No firewall’s

    TCP and namepipes enabled on all

    Any help would be appreciated…..

    PS. – by default Office 2007 business Contact Manager seems to install the SQL server 2005 on all client pc’s …. is that a problem ?

    Thanks !

  21. Karim says:

    I was about to reinstall SQL Server on my server.

    This article saved us hours of extra work.

    Thanks

    Here are the steps I took for our server (SQL server 2005 x64 bit SP2; OS:  Windows 2003 x64 bit)

    1. Shut down sqlserver service.

    2. Switch the sqlserver service logon account to ’Local System’

    3. Start up sql server service

    4. Checked connection to sql server from my workstation  (worked)

    5. Shutdown sqlserver service

    6. set SQLserver and sqlagent services to manual

    7. Switch the sqlserver service logon account to ‘domain/loginid’   (DO NOT START THE SERVICE)

    8. Rebooted the server

    9. Started SQL service manually

    10. Checked connection to sql server from my workstation  (worked)

    11. Switched SQL Server and SQL Agent services to automatic.

  22. madhuri says:

    hi all,

    I got the same problem "Cannot Generate SSPI Context" when  I was trying to run the application. This problem araised when the managment studio was newly installed in my system . so I tried to connect our server to the new management studio and now it is working fine :).

    Thank you

    madhuri

  23. Cammy says:

    Mine works just fine with the client fresh reboot but generate that error after couple minutes….

    Tried all the above that applies to my but still no luck.

    Any idea???

  24. Deepu says:

    hi

    I got the same problem "Cannot Generate SSPI Context" when  I was trying to open my connection.iam using localhost as data source and Integrated security as SSPI.

    Please help

  25. Gabriel says:

    We want to rollout a new account to use for SQL Services. On a Dev server (SQL 2005 SP3, Windows Server 2003 SP2), I changed the account through SQL Configuration Manager. After restarting the services, from my remote desktop, I get an error "Cannot Generate SSPI Context" when I try to connect with Windows Authentication. 'sa' connects fine remotely, and Windows authentication is fine if I log on to the server and open SQL there.

    I've tried various combinations of changing services to LocalSystem, then back to the new account, rebooting between changes, doing steps in different sequence. Once or twice, it has then worked, but when I try later, I get the error again.

    I just changed the Service account to LocalSystem, restarted, changed to my new SQL Service Account, restarted, and it worked – I could connect.

    Then I rebooted the server and got the error again ?!?!?! Back to square 1

    Is there a "best practice" list for changing SQL services to a new account ? MS documentation basically says just change in through Configuration Manager, nothing about "gotchas" or special steps.

    rebooting between changes, doing steps in different sequence. Once or twice, it has then worked, but when I try later, I get the error again.

    I just changed the Service account to LocalSystem, restarted, changed to my new SQL Service Account, restarted, and it worked – I could connect.

    Then I rebooted the server and got the error again ?!?!?! Back to square 1

    Is there a "best practice" list for changing SQL services to a new account ? MS documentation basically says just change in through Configuration Manager, nothing about "gotchas" or special steps.

    dunncrew at hotmail dot com

  26. SQLDeveloper says:

    The following fixed the issue.

    1. Switched the sqlserver service logon account to ‘Local System’

    2. Rebooted the server

    3. Switched the sqlserver service logon account to ‘Domain/Account’

    4. Rebooted the server

  27. Sami says:

    The problem for us turned out to be that Kerberos ports were blocked between the DCs.

  28. Ticl says:

    Got stuck with SCCM 2012 setup due to this error in wizard log. Absolute lifesaver, thanks!

  29. Thomas M says:

    Just had my round with this issue and all standard solutions have failed me.

    So if someone bumps into this, as I did (one of first finds), and hopefully scorll comments to this one:

    In my case the only noticable thing that happened was windows update on that server, no accounts nor network changes. To my surprise reason for "Cannot Generate SSPI Context" was time…

    Not sure yet why, update and following reboot moved system time over 20min ahead. As a result it could not get authentication from domain controller.

    All I had to do, in fact, was to send two NET TIME commands (or one in case you have simpler infrastructure) – described in stevehardie.com/…/how-to-synchronise-your-clock-with-the-domain-controller