SQL Server SSPI Errors

Wow. Its been a while since an update has appeared here, huh? Well, I had an interesting problem at work the other day and I thought I’d share the solution.

A new application is being installed and tested which uses an MSSQL database for its internal information management. Integrated security is used based on a per-user session using Kerberos tokens. This is done automatically by the application connecting to the database using functionality in SSPI (Security Support Provider Interface).

Well, long story short, it wasn’t working. The application spat out the following error each time anyone tried to start it:

Cannot generate SSPI context.

Additionally, various application-specific messages appeared.

As per the link above, most of the troubleshooting tips were based around making sure that the Active Directory user account running the service firstly had rights to log on as a service on machines where it was used, and that it had NT AUTHORITY\SELF entries on the ACL in ADSIEdit allowing readServicePrincipalName and writeServicePrincipalName. This was all correct in our situation, but still the error appeared, even after changing the user account on the service to a new one.

Trawling through the information logs on the SQL server side of things led to the following message being discovered:

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/databases.andinet.local:COMMUNICATOR ] for the SQL Server service. Windows return code: 0x21c7, state: 15.

Interestingly this was reported as information message, not an error message. If it had been logged as an error it would have been spotted earlier in the troubleshooting process.

The key thing in this message is the return code. This particular return code indicates an index violation, that is to say the server was attempting to create a duplicate of something that should be unique. Error codes can be checked with this handy utility from Microsoft.

Jumping on a domain controller and executing the command setspn -l <server name> showed that the SQL instance already had an SPN assigned to it. Running the command produces output like this:

setspn-l-example

To remove the offending SPN entries, a different setspn command is needed: setspn -D <spn> <server> for example: setspn -D MSSQLSvc/databases.andinet.local:COMMUNICATOR databases

This should be run while the SQL instance itself and other all other SQL services running under the user account are shut down. The entire server should then be rebooted. Once the computer account has re-authenticated with the domain and the SQL instance has started up, it will re-register the SPN and the following message will be posted to the information log:

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/databases.andinet.local:COMMUNICATOR ] for the SQL Server service.

Additionally, you can check what transport your connection to the SQL instance is using by executing the following SQL command. Note that results of this might vary depending on how you connect (e.g. via your application, through SSMS or via SQLCMD).

SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Hope this helps. I’ve got a couple more tech-tips in the pipeline relating to Microsoft’s Data Protection Manager 2016 running on Server 2016, but we’ll save those for another day… Let me know if there’s anything specific you want me to cover in the comments and I’ll see if I can help.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email
Bookmark the permalink. Follow any comments here with the RSS feed for this post.
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Your email address will not be published. Required fields are marked *

This blog is kept spam free by WP-SpamFree.