SQL Server Performance

cannot generate sspi context

Discussion in 'SQL Server 2005 General DBA Questions' started by KRN, Jul 31, 2007.

  1. KRN New Member

    Hello,
    We have two dev servers both set up identically and use the same domain user account to run sqlserver services. However ServerA is allowing tcp-ip client connections(sql server client tools) to happen ( it falls back to NTLM authentication), but ServerB will not fall back to NTLM, but produces a Cannot generate sspi context errors. Of course when named pipes is specifically used to make the client connections, then everything goes fine.
    From what I understand about how authentication works in sql server 2005, is that Kerberos is used if available and both the DNS and the SPN can be resolved. SPN resolution is possible if either it is registered manually or if the account the sql server is running under is either a local system account or a domain admin account. The account for sql server is neither of these, so it follows that it cannot register the spn automatically. In fact I can see that in the logs on both the servers. However, then then the authentication should fall back to NTLM using tcp-ip and server A does, but server B does not.
    Anyone know why?
    Thanks
    KR
  2. MohammedU New Member

  3. satya Moderator

    Here is something that is from the SQL Protocols blog: http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx
    There needs to be more information from this problem so that it can accurately be addressed. This sometimes gets manifested by having a User that exists that is a local user like NT AUTHORITYANONYMOUS LOGIN which does exist on each machine, but will not have the same password.
  4. KRN New Member

    Let me know what Information that I can give that will help - I have not found anything in the error logs that might point to this issue except the fact that the account was not able to register the spn and the authentication might fall back to NTLM.
    Following is the exact error that I get when trying to register the server using tcp-ip:
    Testing the registered server falied. Verfiy the server name, login credentials and database and then click test again. Cannot generate sspi context( Microsoft Sql server).
    There is one other thing: If I specify sql server authentication instead of integrated and then use tcp-ip, I do not get any errors. Based on this I was positive that there was probably an incorrect spn registered, but my network admin says that spn was never manually registered ever, and that the service account did not ever have rights to register one. So I am a little puzzled.
    I am not sure I understand your meaning about the NT AUTHORITYANONYMOUS login issue - could you explain a little bit more?
    Thanks much
    KR
  5. satya Moderator

  6. KRN New Member

    You mean domain admin rights? - Best practice reasons. Also, that is what Microsoft recommends that the service account not be given domain admin rights, and should not need it.
  7. satya Moderator

    OK, as long as you haven't enabled local login policy for service account it should be ok as we do on all of our servers except local login.
  8. KRN New Member

    Yep, I have read this article, checked that the IP Address etc resolve correctly. Regarding the SPN, I asked our network admin since I do not have domain rights and he assured me that the spn has never been registered manually. The sql service account is just a domain user so cannot registert the spn automatically - which is why server A falls back to NTLM rather than Kerberos. My question is not why Kerberos is not being used. I realize what I have to do to make that happen. My question is why server B will not fall back to NTLM but produce the error?
    Thanks
    KR
  9. eric.stephani New Member

    Use the setspn executable to lookup the spn's from the domain...
    setspn.exe -l <server name> and setspn.exe -l <domain account running your sql server service>
    Make sure there are no entries that start with MSSQLSvc. If there are, request your Domain Admin remove them. After that use the ldifde utility to search the domain for any spn entries referencing the server.
    ldifde.exe -f output.txt -l servicePrincipalName -r (servicePrincipalName=MSSQLSvc*)
    Seach through this output for the server name and/or the account name and see if any spns are created. Have your domain admin remove any spns referencing the server or account. Frequently I usually find the problem here. Because if you ever change the account the service is running as you may have an spn created for that server/account. Once all these spns are cleaned up, your authentication will fall back to ntlm like it should.
  10. eric.stephani New Member

    Also.....if there is still problems connecting double check the account and password the service is using to start is correct. If the password was changed, but the service was not updated to use the new password these errors might result. Unfortunately, on SQL Server 2000 the service needs to be stopped and started to use the new password. However, with SQL Server 2005, the service account password can be updated without restarting the service.
  11. KRN New Member

    I read that I would need domain admin priveleges to run the setspn executable and I don't , which is why I sent the relevant KB artcile to our network admin to check. I don't know if he actually checked or not, but that is when he replied to my question that we don't register spn ever.
    Thanks
    KR
  12. eric.stephani New Member

    Did you even try it?
    You shouldn't need to be domain admin just to view the spn's. You only need that level to delete the spn's. Unless your Admin explicitly revoked this access, which would be the first time I have ever heard of someone doing.
  13. KRN New Member

    I did and got an error: setspn.exe is not recognized as an internal or external command - I assumed this is because I don't have rights, but maybe I need to download the utility -- again I thought this utility came with windows 2003.
    Thanks
    KRSE
  14. eric.stephani New Member

  15. KRN New Member

    The above link is for windows 2000, yes, but I don't see one for windows 2003 or windows xp - which is what we have here.
    Thanks
    KR
  16. KRN New Member

    Found the tools for xp and 2003
    KR
  17. KRN New Member

    Ok,
    Did all the spn checks - and did not find any registered spn. So I guess I am back where I started, and don't know why the authentication will not fall back to NTLM
    KR

Share This Page