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


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


Comments (69)

  1. Greg says:

    Just wanted to say thank you for this info…

    It just helped me solve my SSPI problems.

    You rock.

  2. Naim says:

    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.

  3. GG says:

    Blessings, You saved me another 3 hours after the 5 I already spent on the issue…

  4. TW says:

    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.

  5. Foxjazz says:

    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.

  6. foxjazz says:

    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.

  7. SQL Protocols says:

    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.

  8. Manoj says:

    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.

  9. Nan Tu says:

    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?

  10. sshah says:

    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.

  11. Andrey says:

    Yes, it was enough to disable TCP/IP, in my case and it works.

  12. Henrik F says:

    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?

  13. Nan Tu says:

    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.

  14. Henrik F says:

    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.

     

  15. Richard says:

    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.

  16. Tushar says:

    Yes, Connecting as localhostsqlexpress solved the problem. Thanks a ton.

  17. Himanshu N says:

    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

  18. Mahesh says:

    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.

  19. SQL Protocols says:

    Hi, Mahesh

       It seems

    your client box is in a seperate domain, normall, you need configure the two domains ( one your client box in and the other your server box in)mutual-trusted, and you need to make sure the account that running under your client app is same as the service account that your SQL Server is running and both of them have administrator permission either in the domain(if it is a domain account) or in the local box( if it is a computer account).

    Good Luck!

    Ming.

  20. rogge says:

    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 \<ip or computer name> /SET /Y

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

  22. This post provides some tips to troubleshoot Sql Server connection problems based on various displayed

  23. anand says:

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

  24. Dove says:

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

  25. Nan Tu says:

    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

  26. Cal says:

    Excellent!  Using 127.0.0.1 as my server instead of my computer name solved my issue.

    Thanks!

  27. Leo says:

    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?

  28. Kitty says:

    Thank you!!!  This worked (and now I understand why) on an ODBC connection for a Crystal Report.  

  29. Brian Travis says:

    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.

  30. Moon says:

    I want to give you a big hug!!!  I’ve been fighting this thing all day and its was making me crazy… now it’s fixed!

  31. You champion. Thanks for this article.

  32. Sameer says:

    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

  33. Genious says:

    Very nice & Informtive Article.

    helped a lot to understand things.

    thans,

  34. RobJ says:

    Another cause of this problem is DNS configuration problems.  I know this sounds simple, but…

    As a developer working from home, i have a domain and a router/firewall.  The firewall is configured to hand out dhcp, the workstations use the firewall and my ad/dns server for dns (isp first, domain second).

    This worked for a very long time, and then all the sudden things got very flaky.  Authentication failures, SSPI Context problems, file transfers started out slow, failed and then worked fine.. etc..

    It seems my cable company no longer returns a DNS Domain Not Found error, EVER.   They return the IP address of their web redirect servers to ‘help’ people find what they were looking for.  Because of this, the windows client does not fail on the first request for the myaddomain.int lookup; and subsequently never queries the internal AD domain controller for the proper addresses of the authentication servers.

    It gets an ip address, and proceeds to try and

    lookup the location of the LDAP server for the internal network; which fails because we’re querying a web server at the ISP for this information, thus resulting in a failure.  The windows client eventually seems to fail over to the secondary dns server or it get’s the names of the domain and servers via NetBIOS broadcast, not sure which. After an indeterminate period of time it would start working.

    After much time and effor the solution was to only use my internal domain controller for DNS resolution and have it relay the external DNS requests out to the ISP’s domain.

    This will be less likely in corporate environments where the DNS configuration should be well planned. In home office networking configurations, it will likely be more common.

    Hope This Helps Someone.

  35. halo says:

    check DNS server in your ip setting

  36. Praveen says:

    Here is my scenario:

    SQL server 2005 is on Windows 2008 box, I have a Windows 2003 VPC and trying to connect the to the SQL server throw SQL Server Management Studio.

    I disabled the fire wall on Windows 2008(so that i  dont have to worry about the ports.  my VPC is registered to a domain mananged by Windows 2008.  I have logged into the vpc as the admin of the domain. I have another box (windows 2008), i can connect to the SQL server from that box.

    Can you let me know what do i do to access the SQL Server 2005 from my VPC.

  37. Der says:

    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

  38. Eric Rausin says:

    Does this error occur on Vista as well?  What about 2008 Server?

  39. kmb says:

    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.

  40. A bit of back ground – for the last so many years I am used to running local Ax installation in my laptop

  41. zuraiki says:

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

  42. basel says:

    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?

  43. jim says:

    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

  44. David Cobb says:

    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

  45. Reddy Umamaheshwar says:

    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.

  46. Rahul says:

    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.

  47. ravi says:

    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?

  48. Mr. Andersen says:

    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.

  49. Satyen says:

    Superb , it helped me solved my problem in Biztalk

  50. jpedroalmeida says:

    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

  51. Mohammed says:

    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

  52. Jay Converse says:

    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.

  53. Pietjegates says:

    I received this error because the password of the "functional user account" running my webservice was expired.

  54. Dean says:

    Thanks for your post Nan Tu – not only was it helpful but very interesting and informative

  55. Sanjay says:

    I received this error

    and solved by restarting "DNS Client" service from services.msc

  56. Marnee says:

    What do you do when none of the followings are true?

  57. justin says:

    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.

  58. Ola says:

    Restart the SQL Server Browser servivce

  59. SharePoint 2013 Administrator Training says:

    Good informational source covering the most of the references related to SSPI.

    <a href="staygreenacademy.com/">SharePoint 2013 Administrator Training</a>

  60. Krishna Reddy Munnangi says:

    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

  61. Kenneth Bucci says:

    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.