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

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

Rate This
  • Comments 66

The “Cannot generate SSPI context” issue is described by http://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 http://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 http://support.microsoft.com/?id=811889 in general and by http://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 http://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

Leave a Comment
  • Please add 3 and 4 and type the answer here:
  • Post
  • Just wanted to say thank you for this info...

    It just helped me solve my SSPI problems.

    You rock.
  • 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.
  • Blessings, You saved me another 3 hours after the 5 I already spent on the issue...
  • 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.




  • What I think is happening is that the connection string we use with scope is the computer name and NOT (local) so it uses named pipes to make a connections.



    If a connection is already been made to sql server, then a security context is established, and when connecting the mechanism first checks to see if a context exists before establishing a new one. Since running sync establishes a connection with the Built/in admin security context, it finds one it can use when running scope.



    Running osql and using sa is not establishing a builtin/admin security context.



    When Scope is run after a reboot, and (named pipes) is not enabled, when it can’t find the security context and tries to establish one with named pipes, and can’t do it.



    The sql-DMO error however is completely different and we are bypassing this error by not waiting for sqlserver to startup if the error occurs and try and connect to sqlserver directly.



    When Gary is finished with his tooling around, I will get him to change the connectionstring to use (local) so it will establish the connection without going through Named Pipes.



    Another symptom of the problem that is related:…



    On the machines here with the development version of SqlServer and the SAC is set to local only, we can’t login using ADO with the connection string (local) but we can login using the computername “Data source = “mycomputername”.



    With Sqlclient .NET it logs in clean all the time every time.



    I think there is something seriously wrong with the ADO connection because of the behavior I am seeing.








    --------------------------------------------------------------------------------

    From: Ben Hoelting [mailto:BHoelting@coloradotc.com]
    Sent: Wednesday, February 01, 2006 11:18 PM
    To: Joe Hickman
    Cc: Joe Dickinson
    Subject: Invalid Handle



    Hey Joe,



    I got your phone msg and I’m afraid I have bad news. The invalid handle msg is due to SQLDMO not being able to connect to SQL Server. I never did figure out how to fix this other than a re-install of SQL server. And not just any re-install, the one where we run through the registry and blow away any key that has SQLDMO. Is this happening on all the machines or just a few? The only thing I can think of is SQLDMO is getting corrupted when we install the new SQL. I’m sure we never saw this on any of the test machines. So, I would try just a straight re-install of SQL 2005. If that does not work uninstall, manually clean the registry and re-install SQL. If that does not work you might have to modify the SCOPE code and remove SQLDMO.

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



  • 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.
  • I am getting the above error for one of the client machine - I have tried creating alias using named pipe protocol also and it gives the same error.
  • 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?
  • 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.
  • Yes, it was enough to disable TCP/IP, in my case and it works.
  • 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?
  • 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.



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





     
  • 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.1\sqlexpress solved the problem

    How do I upsize from Access to SQL Express?

    Thanks.
Page 1 of 5 (66 items) 12345