Bagikan melalui

"Cannot Generate SSPI Context" error message, Poisoned DNS

  • Artikel

Incorrect DNS can lead to various network connectivity issues. In this post, I explain how it affects your connection to SQL Server. In some cases, you may see the well-known "Cannot Generate SSPI Context" error message.

Suppose your SQL Server is located on a machine called HostA. Its IP address is A.B.C.D and its FQDN is HostA.mydomain.com. However, your DNS is poisoned and wrong results are returned to your DNS query. Then, you may have problems to connect to your server. There are two forms of DNS poison.

Case 1:

HostA --> A1.B1.C1.D1, i.e, clients get a wrong IP address for HostA or HostA.mydomain.com

In this case, a client may fail to find the server when connecting to the server using the hostname and/or FQDN. If it happens that another server is listening on A1.B1.C1.D1 on another machine, you may see more confusing error messages, usually, logon will fail. This situation is not very difficult to identify and the reason is obvious.

Case 2:

HostA --> A.B.c.D, i.e. clients can get correct IP address for HostA or HostA.mydomain.com 
A.B.C.D --> HostB.mydomain.com, i.e. The IP address is poisoned.

In this case, client can locate the server correctly and make a connection to the server. However, under the hood, SQL Server client stack will do a reverse lookup and build up a SPN based on the result. In this case, the client get something like this: MSSQLSvc/HostB.mydomain.com:1433
If such a SPN happens exist on the network (more specifically Active Directory), then the client will try to connect to the server using Kerberos. However, the service account of the target SQL Server is not the correct container of the SPN and Kerberos will fail. Clients will see the "Cannot Generate SSPI Context" error message. Imagining the SQL Server on the HostB is turned on and off periodically, the client will see occasional logon failure on the SSPI error message. Note that all these only happen when TCP is used to connect to the server.

For Case 2, if the client is on the same machine with the server (i.e. local connection), it should be OK unless the poisoned DNS coming from Host file. The reason is that, for local connection,  NTLM is usually used. There is one special situation you may see a different logon failure. If the server is a SQL cluster and the cluster IP address is poisoned, the connection from the local machine will fail with the following error message:
Login failed for user ''. Reason: Not associated with a trusted SQL Server connection.

One source for DNS poison is that user put an entry in hosts file (c:WINDOWSsystem32driversetchosts) on the client machine and forgot to remove it when they do not need it anymore. Over the time, this entry may not match rhe dynamic record on DNS server and user will see connectivity issue.

Another good source I can think of: Nowadays, more people use laptop on corp network. Most people don't shutdown their laptop when they take the laptop off the network. This will leave the DNS entry on the DNS server for the specific IP address the laptop was using. Sometime later, another machine jumps in and obtained that IP address and ofcourse will register its own DNS entry for the IP. Then that IP will have two entries on the network. Actually, there might be more records (e.g. SPN) left on the networks if the machine did not properly logoff. All this can cause connectivity issues.

Here are the suggestions on trouble-shooting SQL connectivity issue related to DNS. (Actually, I suggest you always do the following check up the ensure the issue is not because of poisoned DNS)
Under command windows, run the following commands:
1) ping -a HostA (and HostA.mydomain.com)
2) ping -a A.B.C.D (If this only returns HostA, but not FQDN, usually means no DNS entry was found, and HostA is from Netbios)
3) nslookup
>hostA.mydomain.com
>hostA.mydomain.com
>A.B.C.D
>A.B.C.D
You need input FQDN and IP address at least twice in nslookup as you may see different results if there are more than one entry for the IP or FQDN.
If you find any mismatch between server's IP and FQDN, that could be the cause of your connectivity issue.

Please read more posts on this blog for related info:

Understanding Kerberos and NTLM authentication in SQL Server Connections
“Cannot generate SSPI context” error message, when connect to local SQL Server outside domain
“Cannot Generate SSPI Context” error message, more comments for SQL Server
Using Kerberos with SQL Server

Xinwei Hong, SQL Server Protocols

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

Comments

  • Anonymous
    February 06, 2007
    The comment has been removed

  • Anonymous
    April 29, 2008
    We have been seeing and trying to resolve SQL connectivity issue all the time. I guess it would be helpful

  • Anonymous
    November 07, 2008
    Xinwei Hong, this post is quite informative and thanks for that. I'm getting an issue of same sort but not on very first attempt. My application works like below.

  1. It processes 10000 entities sequentially. while processing each entity application reads data related to that entity from SQL server. i.e. for processing each entity application talks to SQL
  2. This is a console application where i trigger the executable and it runs for 24 hours. All goes well with first 500 entities but suddenly application fails to process entities quoting this error ""Cannot generate SSPI context" error message" My doubt is if this is the case with Kerboros authentication it should happen with very first record but it is not occuring there. why would it start throwing this exception in the middle? I would greatly appreciate your inputs on this.

  • 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
    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
    March 04, 2010
    We get this error if use TCP/IP. It works fine if we use Named Pipes.

  • Anonymous
    May 21, 2013
    Esse erro para mim, era por causa do horário do Servidor, verifique data e hora também