none
occasional "Cannot generate SSPI context"

    Question

  • Can any of you experts help me out on this error?

    “System.Data.SqlClient.SqlException: Cannot generate SSPI context”

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


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

     



      
    Tuesday, May 09, 2006 8:47 AM

Answers

  • Thanks for letting us know it's a cluster. Is Fjellvalmue the instance name or the virtual server name? You should use the name of the virtual server when try setspn. (One thing for sure, try setspn on the server name in your connection string.)

    In addition, can you do the following steps to check that your DNS is not poisoned?

    ---- Run nslookup, input your servername multiple times (at least twice), and then input the returned IP addresses multiple times (for each IP address that returned). Make sure your servername and the IP address is one-to-one mapping. Otherwise, you may hit this strange problem. ----

    Thanks

    Thursday, May 11, 2006 9:00 PM
  • I got it resolved.


    I ran ldifde.exe command line utility for the entire domain and dumped the Service principal names

     

    ldifde -f c:\SPNdump.txt -d DC=<domain name>,DC=com -l serviceprincipalname

     

    And then ran a quick search for the SPN for my SQL server and found that the SPN was registered to to a different Domain\service account, I deleted that SPN and recreated with the appropriate service account under which SQL server is running. My problem got solved.

     

    Thanks

    Tuesday, September 09, 2008 7:19 PM

All replies

  • Can you run "setspn -L yourservername"? do you see something like "MSSQLSvc/..."? Please run the command when

    a) server is stopped.
    b) server is started under your domain account and you see the SSPI error msg.
    c) server is started under your domain account and you don't see the SSPI error
    d) server is started under local system account
    You should only see "MSSQLSvc/..." for case d).

    If you don't have setspn.exe, get it from here: http://support.microsoft.com/default.aspx?scid=kb;en-us;892777

    Thanks,

    Tuesday, May 09, 2006 5:13 PM
  • Thanks for you suggestion.

    Please note: This problem is not persistant. I cannot trigger it, nor can I repeat it. It sometimes happen on webserver1, sometimes on webserver2 or WebserverN.

    Your instructions seems to assume the opposite, that the error is persistant and repeatable. This is not the case.

    Please advise.

    Regards,
    Henrik :)
    Tuesday, May 09, 2006 7:26 PM
  • Yes. The "occasional" part is most strange. But, we need you confirm that there is no misconfiguration on your network. We can do further investigation then. Thanks.
    Tuesday, May 09, 2006 8:02 PM
  • Ran the tool. I'm not a cluster/network expert, but

    - the name of the SQL instance is "Fjellvalmue".
    - The active node is "Fjellpryd"
    - The passive node is "Fjellfiol".

    FindDomainForAccount: DsGetDcNameWithAccountW failed!
    Cannot find account fjellvalmue


    setspn.exe -L fjellpryd
    Registered ServicePrincipalNames for CN=FJELLPRYD,OU=Cluster Nodes,DC=fjellflora
    ,DC=os,DC=xxx,DC=no:
        HOST/FJELLPRYD
        HOST/FJELLPRYD.fjellflora.os.xxx.xx

    setspn.exe -L fjellfiol
    Registered ServicePrincipalNames for CN=FJELLFIOL,OU=Cluster Nodes,DC=fjellflora
    ,DC=os,DC=xxx,DC=no:
        HOST/FJELLFIOL
        HOST/FJELLFIOL.fjellflora.os.xxx.xx


    Thursday, May 11, 2006 5:38 PM
  • Thanks for letting us know it's a cluster. Is Fjellvalmue the instance name or the virtual server name? You should use the name of the virtual server when try setspn. (One thing for sure, try setspn on the server name in your connection string.)

    In addition, can you do the following steps to check that your DNS is not poisoned?

    ---- Run nslookup, input your servername multiple times (at least twice), and then input the returned IP addresses multiple times (for each IP address that returned). Make sure your servername and the IP address is one-to-one mapping. Otherwise, you may hit this strange problem. ----

    Thanks

    Thursday, May 11, 2006 9:00 PM

  • I'm a bit uncertain about what you mean by "virtual server name". Are you thinking about the cluster of Windows (named "Fjellsmelle")? I tried this:
    C:\TEMP\Henrik>setspn.exe -L fjellsmelle
    FindDomainForAccount: DsGetDcNameWithAccountW failed!
    Cannot find account fjellsmelle

    We use a default instance on the cluster. "Fjellvalmue" is the name of the instance. In our connection string we use the IP adress of the default instance, which is 139.118.150.167 and maps to "Fjellvalmue":
    C:\TEMP\Henrik>nslookup
    Default Server:  setersoleie.fjellflora.os.xxx.xxx
    Address:  139.118.150.170

    > fjellvalmue
    Server:  setersoleie.fjellflora.os.xxx.xxx
    Address:  139.118.150.170

    Name:    fjellvalmue.fjellflora.os.xxx.xxx
    Address:  139.118.150.167

    Please not that when I run setspn with the IP adress which we use in the connection string it returns nothing:
    C:\TEMP\Henrik>setspn.exe -L fjellvalmue
    FindDomainForAccount: DsGetDcNameWithAccountW failed!
    Cannot find account fjellvalmue

    C:\TEMP\Henrik>setspn.exe -L 139.118.150.167
    FindDomainForAccount: DsGetDcNameWithAccountW failed!
    Cannot find account 139.118.150.167

    I think we've found something. I did 20 nslookups towards the IP adress we use in the connection string, and typically 1-2 of 20 lookups times out. Is the DNS poisoned?
    Action point: We adressing this to our infrastructure team as we speak.

    Also, I found this in the eventlog of the active node:
    Logon Failure:
         Reason:        An error occurred during logon
         User Name:   
         Domain:       
         Logon Type:    3
         Logon Process:    Authz  
         Authentication Package:    Kerberos
         Workstation Name:    FJELLPRYD
         Status code:    0xC000040A
         Substatus code:    0x0
         Caller User Name:    sqladmin
         Caller Domain:    FJELLFLORA
         Caller Logon ID:    (0x0,0x464D43BF)
         Caller Process ID:    7776
         Transited Services:    -
         Source Network Address:    -
         Source Port:    -
     


    Friday, May 12, 2006 7:34 AM
  • As I said, you need to input 'fjellvalmue' multiple times and make sure it always returns the same IP address. Also, input 139.118.150.167 multiple times and make sure it always returns 'fjellvalmue'. If DNS is poisoned, you may also fail in windows logon process.

    Always run setspn against the server name, rather than IP address.

    Looks like 'fjellvalmue' is the virtual server name. Please check Books On Line for details about virtual server name and instance name. You can also see my blog to get some idea http://blogs.msdn.com/sql_protocols/archive/2005/12/05/500013.aspx. The blog does not talk about instance name though. It does not matter since your instance is the default instance.

    Friday, May 12, 2006 5:06 PM
  • We've noticed similiar behavior and have identified it occuring when TCP/IP is used as the protocol to access the SQL server. Named pipes does not introduce the error.

    Our circumstances were as follows:

    1. The web page used integrated authentication and a connection string that utilized "Integrated Security=SSPI"

    2. The SQL server MSSQLSERVER service is using domain credentials, no SPN has been registered to the domain account, SQL 2000 SP 3, SQL services network configuration configured for named pipes and TCP/IP

    3. On the web server, the Enabled Protocols in cliconfg were defined as named pipes and TCP/IP

    We verified that the web server was attempting to use TCP/IP to connect by looking at the registry key:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect

    The value corresponding to the NetBIOS name of the SQL server was in the format:

    839057416:tcp:<NetBIOS name>,1433

    When we changed that value to:

    839057416:np:\\<NetBIOS Name>\pipe\sql\query

    it connected correctly.

    I assume that there's no guarantee it will continue to use named pipes without removing TCP/IP from the Enabled Protocols defined within cliconfg.

    pardon my ignorance, but is there an issue with delegation to a SQL server using TCP/IP for the connection?

    Friday, May 26, 2006 12:37 AM
  • This one is solved. We had to DNS servers in our network. The secondary server was not reachable because of fw-issue. After we ensured all client machines could talk to both AD/DNS-servers, everything was ok.

     

    I hope this can help other people out there.

     

    Stupid mistake.

     

    -h

    Saturday, May 17, 2008 11:07 AM
  •  

    We too have the same issue,

    I changed the order of the TCP/IP (2) and NAMEDPIPES (1) , The cluster is working fine now but still I am getting the "Cannot generate SSPI context"

     

    I checked the NSLOOKUP <server name> several times and it was producing same results from the same DNS server.

     

    Can some shed some light on this.

     

    thanks

    Monday, September 08, 2008 9:38 PM
  • This could come from multiple reasons. It's hard to identify without more info. You can go to

    http://blogs.msdn.com/sql_protocols/

    and search for all links related to SSPI or "Cannot generate SSPI context"

     

    and lists all configuration as Henrik does

     

    Thanks.

    Monday, September 08, 2008 9:49 PM
  • I also noticed this kernros error event ID 4 from System error log.

    Code Snippet
    "The kerberos client received a KRB_AP_ERR_MODIFIED error from the server host/xx-xxx-sql02-c1.xxxxxxxxx.com.  The target name used was MSSQLSvc/xx-xxx-sql02.xxxxxxxxxxx.com:1433. This indicates that the password used to encrypt the kerberos service ticket is different than that on the target server. Commonly, this is due to identically named  machine accounts in the target realm (xxxxxxxxxxxxxxx.COM), and the client realm.   Please contact your system administrator."

     

     

     

    Please someone tell me what does this error means and what has to be done.

     

    Thanks

    Monday, September 08, 2008 9:52 PM
  • I exported the SPN list to a text file using ldifde command line utility and found that the SPN is registered with a different service account, can someone tell me if the SPN has to be registered to the account under which the SQL SERVER service is running.

     

    Thanks

    Monday, September 08, 2008 10:38 PM
  • Please someone shed some light on this.

     

    thanks

     

    Monday, September 08, 2008 11:01 PM
  • Please refer http://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx

    to fix this issue by removing the wrong SPN.

     

    Thanks.

    Monday, September 08, 2008 11:09 PM
  • I got it resolved.


    I ran ldifde.exe command line utility for the entire domain and dumped the Service principal names

     

    ldifde -f c:\SPNdump.txt -d DC=<domain name>,DC=com -l serviceprincipalname

     

    And then ran a quick search for the SPN for my SQL server and found that the SPN was registered to to a different Domain\service account, I deleted that SPN and recreated with the appropriate service account under which SQL server is running. My problem got solved.

     

    Thanks

    Tuesday, September 09, 2008 7:19 PM
  • You never create the spn the virtual name.  On cluster you have to use a domain account that has local admin privs on the sql server.  It has to be the same account for both machines and you have to set the spn on the account that is going to start SQL.  The virtual name or cluster name does not even exist in Active directory so there is no way to set an spn on the account.
    Wednesday, June 24, 2009 12:49 PM
  • It looks that this exception is general error for several problems, in our case it was because the client computer had a different date than the sql server computer, making the dates equal fixed the problem.
    Wednesday, February 08, 2012 5:43 AM