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!
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
/* Description: Check Active Directory for SQL Server SPN entries, and script SETSPN commands, if required. by: Hannah 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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 |
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
Awesome, helped me a lot! thanks Hannah
I’m glad it helped! I especially like not needing to restart the SQL Server instance via the “klist.exe purge” command.
FYI, the following command will allow the SQL Server service account to manage its own Service Principal Names: