SQL Server 2008 “Can’t Generate SSPI Context” error on remote connection

While trying to setup a remote connection to a new SQL Server 2008 installation which uses Windows authentication (aka Kerberos) I encountered this error:

Can't generate SSPI context

A good high level explanation of what causes the error can be found on MSDN and a detailed explanation can be found at Microsoft Support but neither article has any detailed explanation of how to fix the problem.

In my case, during install, I had the server start under my user name when I should have instead had it start under the Network Service group. This is a problem because when user Bob tries to log into the server remotely, my user (the one the server is running under) doesn’t have the necessary permissions to run as Bob so Bob gets an error that SSPI can’t be generated. To fix this I needed to change the user that SQL Server runs under to Network Service. To do this:

  1. Open the Task Manager and go to the Services tab
  2. Click the Services button in the lower right corner
  3. Sort by name and find all the SQL entries
  4. In the “Log on as” column you should see some of the services have a user name. For each:
    1. Right click and select Properties
    2. Select the Log On tab
    3. Make sure This account is selected
    4. Fill in Network Service in the box next to “This account”
    5. Delete both passwords
    6. Click Ok
  5. Restart the SQL Server

This should cause the server to run under Network Service instead of a particular user.

Some extra technical details about my setup:

  • The SQL Server was already setup to accept remote connections
  • The Windows Firewall was already setup to accept incoming SQL requests
  • The SQL Server was configured to accept incoming TCP requests