Unable to generate SSPI context

  • I've setup a new SQL server and am unable to connect to it with SSMS from any machine other than the local machine. When I do, I get an "Unable to generate SSPI context" error.

    Googling this tells me the problem is usually that SQL was unable to register a server principle name. However, I do not believe this is the case for a couple reasons:

    1. I'm running SQL under a domain account that all our other SQL Servers use, so I know that account should have permission to register an SPN.

    2. The SQL Server log contains this entry:

    "The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/<machinename>.<FQDN>:<instancename> ] for the SQL Server service."

    On shutdown, there is a similar message saying the SPN was successfully unregistered.

    3. setspn <serviceaccount> lists:

    MSSQLSvc/<machinename>.<FQDN>:<instancename>

    4. There are no entries in the Windows event logs regarding SPNs.

    5. If I shutdown SQL, setspn no longer lists the entry for this server. When I restart SQL, it lists it again.

    So all those things tell me SPNs are being registered OK. I have never changed the account SQL runs under, so I doubt there are duplicate SPNs.

    Server OS is Windows Server 2008 R2, SQL is 2008 R2 Standard, SP1. There is only a named instance of SQL installed - no default instance.

    Anyone have any ideas?

    Shaun

  • Few Questions:

    •Is it in Windows Authentication Mode or Mixed Mode?

    •With which account you are trying to login? The same you used for installation or different?

    •How did you configure the SQL services?

    •Is Browser service running? (you said, it’s named instance so browser service should be up & running)

  • - Mixed mode

    - Trying to log in with a different account than I installed with. Logging in with my network account, which is defined as sysadmin in sql server.

    - Not sure what you mean. I configured everything via the install program, then used SQL Configuration manager later to add a trace flag (3226) and restarted the service for it to take effect.

    - Browser service is running.

    Based on your question, I tried connecting using the same account I installed it with and got the same error.

  • Did you try ‘sa’ login? I just want to make sure that you can make one successful login. Then we can focus on login specific issue.

  • I can log in on the server itself via Windows authentication, no problem. Restored databases and everything. Server is up and running. Just can't connect from another PC. I AM able to connect from another PC using SQL authentication.. Just not Windows authentication.

  • Are Server & PC on different domains?

  • Nope. Same domains.

  • Search for 'Verify the domain' in following...

    How to troubleshoot the "Cannot generate SSPI context" error message

    http://support.microsoft.com/kb/811889

  • Yeah, been through all that:

    1. Verified.

    2. N/A

    3. Verified.

    4. N/A

    5. Verified.

    6. Verified.

    7. N/A

  • @VickyHarp on Twitter #sqlhelp found the problem - the clock on the server was wrong. It was 7 hours behind the network time. Changed it and all is working now!!

    Thanks for the help!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply