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.

/*
    Description:    Check Active Directory for SQL Server SPN entries,
                    and script SETSPN commands, if required.
    by:             Max Vernon
*/

SET NOCOUNT ON;

DECLARE @ServerPort varchar(255);
DECLARE @MachineName varchar(255);
DECLARE @ServerName varchar(255);
DECLARE @InstanceName varchar(255);
DECLARE @UserName varchar(255);
DECLARE @cmd varchar(255);
DECLARE @domain varchar(255);
DECLARE @SPNName varchar(255);
DECLARE @SPNPort varchar(255);
DECLARE @SkipSPNName bit;
DECLARE @SkipSPNPort bit;
SET @SkipSPNName = 0;
SET @SkipSPNPort = 0;

DECLARE @t TABLE (
    txtOut nvarchar(255)
);

/*
    Get the TCP/IP Port the server is using
*/
SELECT @ServerPort = CONVERT(varchar(255), local_tcp_port)
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;

/*
    Get machine name -> if this server is clustered, return the name of the cluster virtual machine
        , server name -> this is in the format of MACHINE\INSTANCE
        , and instance name -> this will be JUST the INSTANCE name, taken from server name above.
*/
SET @MachineName = CONVERT(varchar(255), SERVERPROPERTY('MachineName'));
SET @ServerName = CONVERT(varchar(255), SERVERPROPERTY('ServerName'));
IF @MachineName = @ServerName --default instance
BEGIN
    SET @InstanceName = '';
END
ELSE
BEGIN
    SET @InstanceName = ':' + SUBSTRING(@ServerName, CHARINDEX('\', @ServerName) + 1, LEN(@ServerName) - CHARINDEX('\', @ServerName));
END

/*
    get the name of the service account SQL Server is using
*/
SET @cmd = 'WHOAMI'
DELETE FROM @t;
INSERT INTO @t (txtOut)
EXEC xp_cmdshell @cmd;
SELECT @UserName = t.txtOut
FROM @t t
WHERE t.txtOut IS NOT NULL;

/*
    Get the DNS Domain Name of the server
*/
SET @cmd = 'net config workstation | find /i "workstation domain dns name"'
DELETE FROM @t;
INSERT INTO @t (txtOut)
EXEC xp_cmdshell @cmd;
SELECT @domain = SUBSTRING(t.txtOut, 38, LEN(t.txtOut) - 37)
FROM @t t
WHERE t.txtOut IS NOT NULL;

/*
    SETSPN Using the Instance Name
*/
SET @SPNName = 'SETSPN -A ' + 'MSSQLSvc/' + @MachineName + '.' + @domain + @InstanceName + ' ' + @UserName;

/*
    SETSPN Using the Instance Port
*/
SET @SPNPort = 'SETSPN -A ' + 'MSSQLSvc/' + @MachineName + '.' + @domain + ':' + @ServerPort + ' ' + @UserName;

/*
    Check to see if the SPNs have been registered
*/

SET @cmd = 'SETSPN -L ' + @UserName + ' | FIND /I "MSSQLSvc/' + @MachineName + '.' + @domain + @InstanceName + '"';

DELETE FROM @t;
INSERT INTO @t (txtOut)
EXEC xp_cmdshell @cmd;

IF (SELECT COUNT(1) FROM @t t WHERE t.txtOut IS NOT NULL) = 1 SET @SkipSPNName = 1;

SET @cmd = 'SETSPN -L ' + @UserName + ' | FIND /I "MSSQLSvc/' + @MachineName + '.' + @domain + ':' + @ServerPort + '"';

DELETE FROM @t;
INSERT INTO @t (txtOut)
EXEC xp_cmdshell @cmd;

IF (SELECT COUNT(1) FROM @t t WHERE t.txtOut IS NOT NULL) = 1 SET @SkipSPNPort = 1;

IF EXISTS (SELECT 1 FROM @t t WHERE t.txtOut = N'''SETSPN'' is not recognized as an internal or external command,')
BEGIN
    DECLARE @msg nvarchar(100);
    SET @msg = N'''SETSPN'' is not recognized as an internal or external command,';
    RAISERROR (@msg, 14, 1);
END
ELSE
BEGIN
    IF @SkipSPNName = 0 
        PRINT (@SPNName)
    ELSE
        PRINT (N'SPN for instance already exists.');
    IF @SkipSPNPort = 0 
        PRINT (@SPNPort)
    ELSE
        PRINT (N'SPN for port already exists.');
END

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:

DECLARE @cmd varchar(20);
SET @cmd = 'klist.exe purge'
EXEC sys.xp_cmdshell @cmd;

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:

SELECT InstanceName = @@SERVERNAME
    , InstancePortNumber = CONVERT(varchar(255), local_tcp_port)
    , dec.auth_scheme
    , USER_NAME = USER_NAME()
    , SUSER_SNAME = SUSER_SNAME()
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;

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