SSPI Context Error – Easy Fix.

Have you ever had just one error message that you wish it would give you a lot more information.  This is an error message which I don’t like.  

What causes this error message to occur?  If you rename a server with Sql Server on it and rename Sql Server with domain level security running Sql Server, you will get this error when you try to connect to Sql Server from a remote computer.  You can Remote Desktop to the server and connect to Sql Server with no issues, but as soon as you try to connect remotely you get this error.  This will also happen if you clone a virtual machine with Sql Server on it and then rename it.

What causes this error message?  The Sql Server with a new name does not automatically create SPN (Server Principal Name) in Active Directory for Sql Server when you have a windows account running Sql Server.  

One way to fix this issue go find your Active Directory admin and have him add SPN records for Sql Server.  The other day when my Windows Admin was doing this, even though the account for the server did not exist as this was a new server, he was getting a duplicate error message.  You have to be a domain admin to add new SPN records.

Here is an article from MS Sql Tips to set the SPN

http://www.mssqltips.com/sqlservertip/2955/register-a-spn-for-sql-server-authentication-with-kerberos/

But we found an easier way after reviewing several articles.  We opened the Sql Server Configuration Management tool, changed Sql Server to start with Local System instead of starting with the Windows Domain account.  Then we restarted Sql Server Engine.  At this point, SPN records were created for Sql Server.  We then switched the Sql Server startup account back to a Windows Domain account and restarting Sql Server again.

After doing this, we were able to login remotely to our Sql Server.  I sure wish I would have found this years ago.  Note, we were on a Windows 2012 and Sql Server 2012 server.

Advertisements

One thought on “SSPI Context Error – Easy Fix.

  1. For what it’s worth, Microsoft released a tool last year (Kerberos Configuration Manager) that is pretty awesome. It connects using your AD credentials and checks for SPN issues. If it finds missing/redundant ones, it can either fix or script out a fix. For us it’s been damn near a silver bullet. And the best part is – it’s a MS tool, so Systems types don’t go “it’s the DBA, what’s he know about our Active Directory”, instead they go “oh, MS says it’s a problem, and all I have to do is let you run the fix…?”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s