Enable Kerberos Authentication without rebooting SQL Server

What is this Kerberos thing, anyway?!

Kerberos Authentication1 allows SQL Server to impersonate Active Directory users to other services via double-hop-authentication. This is commonly used to provide access to data through a web-site via Windows Authentication where the identity of the website is passed through to the SQL Server. It is also commonly used in SQL Server Linked Servers to authenticate the end user to the linked-to SQL Server.

Kerberos, the nasty dog!

    Kerberos, The Nasty!

How do I make use of this thing?

When using an Active Directory service account to run SQL Server, that account by default won’t have the ability for SQL Server to create and delete the Service Principal Names, or SPNs that are required for Kerberos Authentication. You can provide the ability for that service account to manage its own SPN entries in Active Directory by following this method to grant the required “Read servicePrincipalName” and “Write servicePrincipalName” rights to the SQL Server Service Account. However, for SQL Server Failover Clusters, self-managed SPNs can be problematic if you have more than a single Active Directory controller visible to the failover cluster nodes. In those environments you probably want to manage Kerberos SPNs manually.

Manually enabling Kerberos authentication typically requires creating a Service Principal Name, or SPN, for the SQL Server instance, and rebooting the instance. The script below will automatically generate SETSPN commands for SPNs that are missing from Active Directory for the current SQL Server Instance. For the code to work, you need to have xp_cmdshell enabled.

The code above will output two lines indicating the command required to create the SPN, or a message indicating the SPN already exists. A system administrator with the correct access to Active Directory can use the commands to create the required Service Principal Names at anytime.

Paying for my keep

Creating those SPNs in Active Directory is great, however the SQL Server Service won’t know about them until you update the Kerberos tickets for the service account on the SQL Server. Most of the time, the SQL Server is rebooted to accomplish this, however, you don’t actually need to reboot the instance, you can simply execute this command from SQL Server to purge and refresh any existing Kerberos tickets:

Once the above command completes, SQL Server should allow Kerberos Authentication, which you can check by re-connecting to the instance and issuing this command:

If you see KERBEROS listed under the auth_scheme column, then Kerberos authentication is working.

Please do me a favor, and subscribe to get notified of new blog posts as they are published.

This post is part of our ongoing series on configuring SQL Server.

1 – See https://web.mit.edu/kerberos/ for more on Kerberos Authentication