occasional "Cannot generate SSPI context"

occasional "Cannot generate SSPI context"

• David Smitty

  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
  
  
  
  
  

• mjj0000

  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.

  
  
  

• Breakpoint

  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,

  
  
  

• Tiduske

  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 :)
  
  
  

• laboremus

  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.
  
  

• cab15

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

• ViLiO

  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

  
  

• sbonomi

  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

  
  
  
• occasional "Cannot generate SSPI context"