“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 2 and 5 and type the answer here:
  • Post
  • You champion. Thanks for this article.

  • 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

  • Very nice & Informtive Article.

    helped a lot to understand things.

    thans,

  • PingBack from http://movies.247blogging.info/?p=3422

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

  • check DNS server in your ip setting

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

  • 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

  • PingBack from http://kimora.freemusiconlineindia.info/cannotgeneratesspicontext.html

  • PingBack from http://damian.mediacentermovie.info/cannotconnecttosqlserversecondinstance.html

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

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

  • PingBack from http://cesardiaz.es/wordpress/?p=9

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

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

Page 3 of 5 (66 items) 12345