Bagikan melalui


“Cannot generate SSPI context” error message, when connect to local SQL Server outside domain

The “Cannot generate SSPI context” issue is described by https://support.microsoft.com/?id=811889 in general. In this post I will discuss one daunting case of “Cannot generate SSPI context” error message when failing to connect to SQL server. In most related cases, customers report this issue as "I can connect to my local SQL Server, but once I connect to my network, I can't connection to my local SQL Server". Such issue is reported against MSDE and SQLExpress. But actually, it can happen with any SKU of SQL Server, including SQL Server 2000 and SQL Server 2005, that support NT integrated authentication. The error message for the failed connection that we discussed here is

 

[SNAC] “[SQL Native Client]SQL Network Interfaces: The Local Security Authority cannot be contacted.[ SQL Native Client]Cannot generate SSPI context”
[MDAC] “Cannot generate SSPI context”;
[.Net1.0/2.0]” Failed System.Data.SqlClient.SqlException: Cannot generate SSPI context”

 

It can happen when all of followings are true:
(1) The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain.
(2) The OS of the hosting machine is Windows XP or 2000. Not windows 2003.
(3) The connection is to a local SQL Server.
(4) Connection configuration causes network library to choose TCP/IP provider.

 

A scenario that meets all of (1) (2) and (3) looks like an extreme corner case. But the reality is that it is quit often if the hosting machine is a laptop computer. One solution, of course, is to avoid condition (1) by connecting to your corporate domain through VPN or disconnecting from network completely. The reason why they work is subtle and I’ll discuss it later. From user’s perspective, however, in many cases, either connecting over VPN or disconnecting from network might prevent you from accessing some valuable resources, so I want to discuss solutions that do not depend on (1) first.

 

In most cases, users do not explicitly require TCP/IP as the connection provider. For example connection strings in form of “.<instance>”, “(local)<instance>”, “<servername><instancename>” are among them. Users might wonder why network library chooses TCP/IP provider instead of Shared Memory provider, if the connection string is not prefixed with “tcp” and the server is local. A simple answer is that it can happen if the TCP/IP provider is in front of other providers in the client protocol order list, or/and the local server is not listening on Share Memory and Name Pipe. As described above, only TCP/IP provider has the issue; hence, configuring network library not to choose TCP/IP is a solution. To do that, first, on the server side, make sure your server is listening on Shared Memory or/and Named Pipe connection requests; then, on the client side, change the protocol order list such that Shared Memory and/or Named Pipe are in front of TCP/IP, or prefixing your connection strings with “lpc” or “np” to force Shared Memory or Named Pipe, or using alias that prefix Named Pipe in connection strings, whichever you feel most comfortable with. Note that certain SKUs of SQL Server have named pipe connection turned off by default.

 

In very rare case, however, if you really in need of TCP/IP connection, the option is to use TCP/IP loop-back address, i.e. “127.0.0.1”, as your <servername>. For example, if your connection string has form of “<servername><instancename>” and is not prefixed with “tcp”, without modifying the connection string, you can configure an alias with alias name as <servername><instancenane>, protocol as TCP/IP, server as “127.0.0.1<instancename>” or “127.0.0.1,<port>”. Remember that the “Cannot Generate SSPI context” problem described in this post only happens when connecting to a local server; thus, the “127.0.0.1” is applicable. If the connection string is prefixed with “tcp”, then you do need to modify your connection string to specify “127.0.0.1” as <servername>.

 

If these workarounds described above do not fit your needs, we would like to hear more from you.

 

The reason that we didn’t fix this subtle issue is because the limitation is rooted in a behavior of an integrated authentication module (SPNEGO) in XP and windows 2000, i.e. whether to fallback to NTLM if KDC is not available when the target SPN points to local machine. KDC, normally, is part of your domain controller. For this specific case, SPNEGO chooses not to fallback, hence connection fail. This issue is not a security issue though. Reader might ponder why avoiding using TCP/IP provider can solve the problem while explaining it is because certain behavior of SPNEGO in Windows. Not going too deep, the simple answer is that only TCP/IP provider, with an exception of loop-back connection, uses SPNEGO while other providers use NTLM. Be aware that only TCP/IP provider can provides the benefits of Kerberos authentication as discussed in https://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

 

Back to the questions we left before, the reason that disconnected from network (no network media) works is because, in such case, local <servername> is resolved to “127.0.0.1” by windows network layer and NTLM is used directly. When connected over VPN, the SPNEGO issue goes away because the KDC is accessible in this case.

 

From the error message reported by SNAC ODBC/OLEDB, you can differentiated the issue described by this post from another case of “Cannot generate SSPI context”, in which the root cause is because, in Active Directory, the Service Principle Name (SPN) of SQL Server is registered for a domain account different from the SQL Server is actually running under. The error message for the other case is “[SQL Native Client]SQL Network Interfaces: The target principal name is incorrect.[SQL Native Client]Cannot generate SSPI context. The “Cannot generate SSPI context” issue is described by https://support.microsoft.com/?id=811889 in general and by https://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx specifically for the other case.

 

Do you know that you can post question w.r.t SQL Server data access, connectivty issues at https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1 ?

 

Nan Tu, Software Design Engineer, SQL Protocols

 

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

  • Anonymous
    November 04, 2005
    Just wanted to say thank you for this info...

    It just helped me solve my SSPI problems.

    You rock.

  • Anonymous
    January 03, 2006
    Interesting issue, and definitely not something I've run into before. But, now that I have I'm glad I've found this article. Thanks for posting this.

  • Anonymous
    January 10, 2006
    Blessings, You saved me another 3 hours after the 5 I already spent on the issue...

  • Anonymous
    January 26, 2006
    Thank you very much for this article. I've just spent an hour trying all sorts of fixes, only to find changing my server setting to 127.0.0.1 from 'localhost' solves the issue.

    I agree the situation of a machine joined to a domain but separated from it and running SQL locally is quite common.

  • Anonymous
    February 03, 2006
    The comment has been removed

  • Anonymous
    February 03, 2006
    The error message:





    Before Connect String in Initialize= Provider=SQLOLEDB.1;Trusted_Connection=Yes;Data Source=33BF451THOMAS;Initial Catalog=NAPAScope

    Msg occurred at 2:03:20 PM







    State = 0

    Msg occurred at 2:03:20 PM







    -2147467259 Microsoft OLE DB Provider for SQL Server - ScopeDataEngine = Cannot generate SSPI context

    Msg occurred at 2:03:20 PM











    Informatin: All of this on a laptop.



    We installed sql express with remote connections enabled and tcp/ip enabled.

    When we login to the network via vpn, we are able to login ok.

    When we reboot, and login to the local system, we get the error you see above in the email …. “Cannot Gen SSPI context”

    We then set the Configuration to Local ONLY using the SAC and the ole DB connection connects perfectly to the database.



    The connection string has a data source of (Local) when it fails as well as the one above.



    One solution we can live with is an automated way to set the SAC to Local Only using commands with some batch file.

    Another solution is to find a fix for the OLE DB Connection either the string, or the actually driver that connects to SQL Express.



    All of that said, we tried connecting using a program with .NET SqlClient. Using the same connection string, we were able to connect with SqlClient .NET provider in EVERY case. However since our main software uses OLE DB, we are stuck with the possible suggested solutions.



  • Anonymous
    February 03, 2006
    One of the following should fix your problem:

    (1) Use the new SQL Native Client provider instead of SQLOLEDB by specifying "Provider=SQLNCLI". This assumes that SQL Native Client is installed on the machine, which is the case if SQL Express is installed.

    OR

    (2) Specify the use of the Named Pipes protocol in the connection string by adding ";Network Library=dbnmpntw" to the connection string. That will avoid the use of Kerberos authentication protocol.

  • Anonymous
    February 17, 2006
    The comment has been removed

  • Anonymous
    February 20, 2006
    Manoj,
     Is it a local or remote connection? Is your client machine in domain or out of domain? If it is local connection, does connection using tcp:127.0.0.1 work for you when the server TCP protocol is turned on?

  • Anonymous
    March 18, 2006
    Thanks for info. I use local server for devel purpose in my laptop and was unable to connect while using home network. After disconnecting home network, it worked...Then re-enabled local connection.

    Thank You.

  • Anonymous
    April 27, 2006
    Yes, it was enough to disable TCP/IP, in my case and it works.

  • Anonymous
    May 08, 2006
    I also get this error on client machines, (W2003) using a standalone SQL Server 2000 running W2003AS. “System.Data.SqlClient.SqlException: Cannot generate SSPI context”

    The weird thing is that this only happens occasionally, about 20 times per day.

    Windows 2003 Advanced Server SP1
    SQL Server 2000 SP4
    Not much load on the system
    Application layer: 8 web servers Net 1.1. and two Biztalk 2002 servers
    We use TCP/IP as a connection provider
    The clock on the servers are in sync

    Any ideas?

  • Anonymous
    May 08, 2006
    Henrik,

     In your case, we need to know what is the connection string, "what is the machine account that your server is running under", "do you change account often", "Are client and server unning under different domain" and etc.  Please post your question with more specific info such as connection string on

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1,

    There is a general guideline on the forum w.r.t to how to post a question.



  • Anonymous
    May 08, 2006
    Thanks for your reply.

    [1] Client side:

      1. What is the connection string in you app or DSN? (please specify)
      "server=mydbserver; database=mydatabase; uid=; pwd=; trusted_connection=yes; Max Pool Size=10; Connection Timeout=60; Packet Size=4096; ;"

     
      2. If client fails to connect, what is the client error messages? (please specify)
      Category: 300
      Computer Name: KATTEFOT
      Event Code: 0
      Record Number: 54
      Source Name: AltInn.eGA
      Event Type: Error
      User:
      Time Written: 20060508160522.000000+120
      <ACALog><LogCategory>UnknownException</LogCategory><Header>Unknown Exception</Header><EventID>0</EventID><Body><ExceptionType>SqlException</ExceptionType>
      <UserId>08057833557</UserId>
      System.Data.SqlClient.SqlException: Cannot generate SSPI context.

     
      3. Is the client remote or local to the SQL server machine?
      It is remote.
     
      4. Can you ping your server?
      Yes.


      5. Can you telnet to your SQL Server?
      Yes
     



      6. What is your client database provider?
      Client app is .Net SqlClient Data Provider. It uses MDAC 2.82.1830.0 on both client and server machine.

     
      7. Is your client computer in the same domain as the Server computer?
      Same domain
     
      8. What protocol the client enabled? [Shared Memory | TCPIP | Named Pipes].
      Can you configure this on the client? We're using the "SQL Server .NET Data Provider" and I belive this protocol uses the
      the default protocol of the server, which is 1) TCP/IP and 2) Named pipes.
     
      9. Do you have aliases configured that match the server name portion of your connection string?
      The clients are always using the IP adress of the db-server



    [2] Server side:



      1. What is the MS SQL version?
      SQL Server 2005
      2. What is the SKU of MS SQL?
      Enterprise
      3. What is the SQL Server Protocol enabled? [
      TCPIP and Named Pipes
      4. Does the server start successfully?
      Yes
     
      6. What is the account that the SQL Server is running under?
      Domain Account
      7. Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider?
      YES
      8. Do you make firewall exception for SQL Browser UDP port 1434?
      YES



    [3] Platform:

      1. What is the OS version?
      Windows 2003 Enterprise edition
      2. Do you have third party antivirus, anti-spareware software installed?
      No.





     

  • Anonymous
    June 02, 2006
    I could login to SQL Express this morning, from a disconnected laptop.  Later, with a web connection, but no domain connection, I got the SSPI error

    logging on as 127.0.0.1sqlexpress solved the problem

    How do I upsize from Access to SQL Express?

    Thanks.

  • Anonymous
    July 31, 2006
    Yes, Connecting as localhostsqlexpress solved the problem. Thanks a ton.

  • Anonymous
    August 16, 2006
    Hi,
    I am having the same problem, and the issue here is that the user is using the Win XP and the SQL serve is Hosted on Win 2000. The user is in a seperate doamin and the server is as well. The rights have been given to the user within the SQL server for access. When the user tries to access one server he is able to without any problems, but when he tries to connect to other server in same donain he gets this SSPI error.

    Hopy you can help me on that.

    Regards,
    Himanshu Nirmal

  • Anonymous
    September 26, 2006
    Hi,
    I am facing this problem over VPN, I am not able to connect my SQL Server hosted at Different Domain. While connecting with SQL Server Client, it is giving me same error "Cannot generate SSPI context". Please update me. What is the actual process has to be follow to get resolve this issue.

  • Anonymous
    September 27, 2006
    The comment has been removed

  • Anonymous
    October 02, 2006
    I checked the system log and was told the SPS server could not connect to a timing/clock computer... I ran the net time command to sync the SPS server time with our domain server and that eliminated the problem. net time &lt;ip or computer name> /SET /Y

  • Anonymous
    January 02, 2007
    The comment has been removed

  • Anonymous
    January 28, 2007
    This post provides some tips to troubleshoot Sql Server connection problems based on various displayed

  • Anonymous
    January 29, 2007
    “Cannot generate SSPI context” error message, when connect to local SQL Server outside domain

  • Anonymous
    April 10, 2007
    This happens to me, and anything you say does nothin, neither syncronizing time nor doing the other things.

  • Anonymous
    April 10, 2007
    Dove,  Can you describe what is your configuraiton, including machine, account, connection string, sql server and etc. and when it connect, when it dosn't. There could be ton of reasons. Without your input, we don't have clue to help you out. Nan Tu

  • Anonymous
    April 22, 2007
    Excellent!  Using 127.0.0.1 as my server instead of my computer name solved my issue. Thanks!

  • Anonymous
    June 26, 2007
    I'm having this error in a different situation: If I'm trying to run: osql -S 127.0.0.1 ... then I get the error but if I run: osql -S <name> ... then I don't The machine is a domain controler Win2K with SQL 2K on it - all latest patches applied. Any clue?

  • Anonymous
    June 26, 2007
    Thank you!!!  This worked (and now I understand why) on an ODBC connection for a Crystal Report.  

  • Anonymous
    August 19, 2007
    Changing the database name to 127.0.0.1 (using the default SQL instance) fixed it! Thanks for the excellent post; it saved my life while I was disconnected from the mother ship.

  • Anonymous
    September 04, 2007
    The comment has been removed

  • Anonymous
    September 26, 2007
    You champion. Thanks for this article.

  • Anonymous
    October 04, 2007
    From ssms of one system iam able to register other system having ssms. But when i do the same from SQL enterprise manager of one system to ssms of another,i get "Cannot generate SSPI context" message. Both the system are in same domain. Thanks

  • Anonymous
    November 27, 2007
    Very nice & Informtive Article. helped a lot to understand things. thans,

  • Anonymous
    January 01, 2008
    PingBack from http://movies.247blogging.info/?p=3422

  • Anonymous
    January 09, 2008
    The comment has been removed

  • Anonymous
    April 09, 2008
    The comment has been removed

  • Anonymous
    April 16, 2008
    The comment has been removed

  • Anonymous
    May 14, 2008
    Pocket PC connect SQL Server is Error "Cannot generate SSPI context"  Becuase ?. How do ?.  To connect sql server on PC with Connect Active syncronize. Thank you

  • Anonymous
    May 20, 2008
    PingBack from http://kimora.freemusiconlineindia.info/cannotgeneratesspicontext.html

  • Anonymous
    July 10, 2008
    PingBack from http://damian.mediacentermovie.info/cannotconnecttosqlserversecondinstance.html

  • Anonymous
    July 14, 2008
    Does this error occur on Vista as well?  What about 2008 Server?

  • Anonymous
    August 25, 2008
    I'm sure there are many complex causes for this SSPI context error. However, the simplest case isn't covered here or in the MS KB. If you change the service account password but do not change it in the SQL service credentials and leave the SQL instance running, you will get this error trying to connect with Windows authentication. Put the correct new password in the service credentials and it's fixed.

  • Anonymous
    September 01, 2008
    PingBack from http://cesardiaz.es/wordpress/?p=9

  • Anonymous
    October 27, 2008
    A bit of back ground - for the last so many years I am used to running local Ax installation in my laptop

  • Anonymous
    November 12, 2008
    Im using BizTalk server and SQL server, i faced the same Error .. the DATE was not the same on BizTalk server and SQL server !!! one of the guys did change the date. after changing the date and make it the same on the 2 servers everything ran fine!!!

  • Anonymous
    January 14, 2009
    iam using the Win XP and the SQL serve is Hosted on Win 2000...i keep getting disconnected from SQL D.B every hour and SSPI context error shows up...i log off the pc then log in again and then i can log in to the D.B again...how can i stop it plz?

  • Anonymous
    January 18, 2009
    PingBack from http://www.hilpers.it/2538994-contesto-sspi

  • Anonymous
    January 18, 2009
    PingBack from http://www.keyongtech.com/2862529-vpn-and-cannot-generate-sspi

  • Anonymous
    May 16, 2009
    PingBack from http://tagz.in/posts/4gl/comments/

  • Anonymous
    May 21, 2009
    Hi,  I installed SQL Express in Windows server and tried to connect with it, getting this error...but it connects with the SQL authentication...when I tried to connect it with Windows authentication. I'm getting this error.....plz help me

  • Anonymous
    May 31, 2009
    PingBack from http://woodtvstand.info/story.php?id=8365

  • Anonymous
    June 15, 2009
    I'd like to script this so I can ensure Named Pipes is enabled on the SQL 2008 server and the SQL 2008 client has named pipes enabled and the order is BEFORE tcp/ip. How can I script this? Not finding it in sp_configure.. Please reply or email sql@davidcobb.net

  • Anonymous
    September 18, 2009
    I disabled TCP/IP, enabled Shared Memory, and Named Pipes options from SQL Server Configuration Manager on my Microsoft SQL Server 2005, restarted the Server. I was able to get out of this error. Thanks a lot to this posting.

  • Anonymous
    November 10, 2009
    Please help me out!! I have been trying to connect but getting this error msg "Cannot generate SSPI context". I need a access a BAK file on server using SQL.

  • Anonymous
    November 11, 2009
    i have ms access based application linked to ms sql server. when i try to connect this application through a client machine (winXP), i am getting unablae to generate SSPI context error. but in the same machine, when another user log in he is able to connect to server without problems? is there a possibility that this problem is linked to user account?

  • Anonymous
    January 04, 2010
    I just experienced this error again, on a computer that is a member of a domain, but where the computer is disconnected from the network. The error occurs not only when using the TCP/IP protocol, but also when using Shared Memory (I tried disabling all other client protocols and also all other server protocols). The error I get, when trying to use the osql.exe utility, to connect to the server, looks like this: [SQL Server Native Client 10.0]SQL Server Network Interfaces: The Local Security Authority cannot be contacted [SQL Server Native Client 10.0]Cannot generate SSPI context The computer has a loopback interface which allows it to still use TCP/IP, but doesn't allow it to connect to anything. The only workaround that has worked on this computer has been to enable the TCP/IP protocol again and connect to 127.0.0.1/InstanceName instead of using .InstanceName or ComputerNameInstanceName.

  • Anonymous
    February 17, 2010
    Superb , it helped me solved my problem in Biztalk

  • Anonymous
    April 07, 2010
    I there, I have just experienced this error and the problem was in the server where SQL Server instance was running because the time in Operating System was not correct and assincronous from the Active Directory server time. The error was solved fixing the time in the operating system where SQL Server was running. Cheers

  • Anonymous
    July 26, 2010
    Hi , im facing problem cannot generate sspi context  after some time the application is open.  once i logged off and login again, im able to connect for some duration, again it will raise the same error message.   Please guide me on this

  • Anonymous
    September 07, 2010
    Bless you!  I'm the classic laptop software professional, with a domain in the office, and no domain at home.  Changing my code from my laptop's name to 127.0.0.1 was the trick.

  • Anonymous
    December 07, 2010
    I received this error because the password of the "functional user account" running my webservice was expired.

  • Anonymous
    January 05, 2011
    Thanks for your post Nan Tu - not only was it helpful but very interesting and informative

  • Anonymous
    February 16, 2012
    The comment has been removed

  • Anonymous
    April 12, 2012
    What do you do when none of the followings are true?

  • Anonymous
    July 31, 2012
    that was awesome. I ve been searching for 2 hours till I got this article , which fixed my issue in a minute. Hats off to you.

  • Anonymous
    October 09, 2013
    Restart the SQL Server Browser servivce

  • Anonymous
    April 22, 2015
    Good informational source covering the most of the references related to SSPI. <a href="staygreenacademy.com/">SharePoint 2013 Administrator Training</a>

  • Anonymous
    September 25, 2015
    This issue might also happen if the service account is changed and password is not updated. You are able to connect to the SQL Server on that machine. Not able to connect the SQL Server from the remote or different machine

  • Anonymous
    November 19, 2015
    I had this issue and nothing posted in many forums, blogs Microsoft Support and TechNet articles solved my problem. Nothing in this post worked for me. I ran into this issue on a Windows Server 2012, running both SQL Server 2012 Express and SharePoint 2013 Foundation Services. How I fixed my issue is in Services.msc I stopped the SharePoint Search Host Controller service which was running, but disabled on installation of SharePoint. The issue is gone. How the service got started is beyond me.