That “Cannot Generate SSPI Context” headache

Problem : You have completed an SQL Server setup on a new instance. The setup is successful, you can connect locally and and everything is fine. No headaches. Now to test your SQL Server, you try to connect from a client PC and you get that “Cannot Generate SSPI Context”  error. Now what?

Most likely you have the same situation as below;

  • You are using Integrated Security.
  • Kerberos authentication is used to perform the security delegation. You can verify this with your domain admin.
  • You are not using Local System account in your SQL Server service which is a good practice.

Solution:

What SSPI?

Security Support Provider Interface (SSPI) is a set of Windows APIs that allows for delegation and mutual authentication over TCP/IP sockets. Therefore, SSPI allows for a computer that is running a Windows operating system to securely delegate a user security token from one computer to another over any transport layer that can transmit raw bytes of data.

What SPN?

Another term that we have to define here is SPN. SPN stands for Service Principal Name. Kerberos authentication uses an identifier named “Service Principal Name” (SPN). Consider an SPN as a domain or forest unique identifier of some instance in a server resource. You can have an SPN for a web service, for a SQL service, or for an SMTP service. You can have a multiple SPNs if you have multiple instance sitting on your database server.

Setting SPN

If you are using a domain user account for your SQL Server service, you need to manually register the SPN for this instance to create the SPN in your Active Directory. The SPN for SQL Server has the following elements;

  • ServiceClass: This identifies the general class of service. This is always MSSQLSvc for SQL Server.
  • Host: This is the fully qualified domain name DNS of the computer hosting SQL Server.
  • Port: This is the port number that the service is listening on. Unless you have made changes to the default port during your setup, this value is 1433.

To register the SPN for the domain user account in your Active Directory, you may run the following command in your cmd window;

SETSPN -A MSSQLSvc/<SQLServerName>:1433 domain\useraccount

The trick here is that you need to issue this command twice. One for the NETBIOS name, which is the above command, and one for the FQDN (Fully Qualified Domain Name) to allow for the setup to succeed.

So you will have to run also the below as your next step.

SETSPN -A MSSQLSvc/<SQLServerName.domain.com>:1433 domain\useraccount

For multiple instances, you will then need to run it twice for each instance that you have. There are times, when you will need the help of your friendly domain admin to run the setspn command because your current permission is not enough. Also to make sure this is successful, you will need to check that the SQL Browser service is running during the setup, because it will be looking for it (especially for multiple instances) and validate it.

To validate that the setspn command was successful, in your cmd window;

setspn – L domain\useraccount

This should list all the instances you have in your server.

One less headache… 🙂

 

Advertisements

6 responses to “That “Cannot Generate SSPI Context” headache

  • Kenneth Fisher

    Nice job. I hadn’t realized you had to set up the SPN twice.
    Thanks!

  • Amir

    As far as I faced the issue before,
    If you assign a proper domain account during the installation time or using sql server configuration tool (after normally stopping the service) you won’t face this situation.
    The problem occurs when a SPN was registered via another account and the unregistering process had not happened in the right way (i.e. Stopping sql service using sql configuration tool) .

    Microsoft recommends changing service account using sql configuration tool.

    Anyway, nice job. I am sure many have faced this headache.
    Thank you

  • Ervin Steckl

    Yet another point: you may need to check for duplicate SPNs in your domain/forest. Issue the SETSPN -x command for this. Add the -F switch for forest-wide checking.
    SETSPN info: http://blogs.msdn.com/b/psssql/archive/2009/02/13/searching-for-duplicate-spn-s-got-a-little-easier.aspx

  • Daniel Nicoletti

    if the SQL engine account has the correct AD permissions, the SQL registers the SPN automatically on start-up.

    if you have a look though your SQL logs:

    When your permissions are not set correctly (or you’ve set SPNs manually as the business policy wont allow SQL to have the required rights) you’ll see this:

    “The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service”….

    …”Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.”

    or when configured correctly:

    The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ XXXXXXXX/XXXXXXX ] for the SQL Server service.

    The permissions the SQL service requires are the “Read servicePrincipalName” and “Write servicePrincipalName” access control settings in the Active Directory service.

    here is the Microsoft article that deals with the SPNs and permissions:
    https://msdn.microsoft.com/en-us/library/ms191153(v=sql.105).aspx

    see: Automatic SPN Registration – and permissions required in the permission section links.
    (note I don’t advise giving the SQL service account domain admin privileges!)

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

SHE GOES GLOBAL

Travel the world, change your life.

My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: