Script to Check and Auto Generate SPNs for SQL Server

Problem

Ben Snaidero wrote a nice tip about how to Register a SPN for SQL Server Authentication with Kerberos. I want to make sure SPNs are registered for all SQL Servers that I manage, however I manage hundreds of SQL Servers and I need an easy way to check and generate SPN commands without worry about making mistakes or forgetting to register SPNs.

Solution

I wrote a SQL script to use a combination of xp_cmdshell (yes, I know it is not preferred, but you can change the script to enable it right before running the script and disable it once the script is done) and other DMV queries to check and generate SPN commands so you can provide the script to your system’s team to run.

NOTE: Depending on your environment, this query may take 15+ seconds to run so please be patient.

Here is the code. You can also download here.

/*
== Description == 
This script will check SPN and generate script like below if missed
   SETSPN -S MSSQLSvc/YOURSERVERNAME:1604 mydomain\svcAcct 
   SETSPN -S MSSQLSvc/YOURSERVERNAME.mydomain.com:1433 mydomain\svcAcct
== LIMITATION == 
- Make sure your sql server is not using DynamicPort
- If the server is part of AG group, this script won't check AG Listner 
*/
SET NOCOUNT ON
 
-- service account
DECLARE @DBEngineLogin VARCHAR(100)
 
EXECUTE master.dbo.xp_instance_regread
   @rootkey = N'HKEY_LOCAL_MACHINE',
   @key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
   @value_name = N'ObjectName',
   @value = @DBEngineLogin OUTPUT
 
-- SELECT [DBEngineLogin] = @DBEngineLogin
 
DECLARE @physicalServerName varchar(128) = '%' + cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(64))+ '%'
DECLARE @ServerName varchar(128) = '%' + cast(SERVERPROPERTY('MachineName') as varchar(64)) + '%'
DECLARE @spnCmd varchar(265)
 
SET @spnCmd = 'setspn -L ' + @DBEngineLogin
CREATE TABLE #spnResult (output varchar(1024) null)
INSERT #spnResult exec xp_cmdshell @spnCmd
 
CREATE TABLE #spnLIst (output varchar(1024) null)
 
INSERT #spnLIst
SELECT output as 'SPN List for Service Account' FROM #spnResult
WHERE output like @physicalServerName or output like @ServerName
 
Declare @NodeName VARCHAR(128)
DECLARE db_cursor CURSOR FOR  
SELECT '%' + NodeName + '%' AS NodeName FROM fn_virtualservernodes()
 
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @NodeName 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
   INSERT #spnLIst
   SELECT output as 'SPN List for Service Account' FROM #spnResult
   WHERE output like @NodeName  
 
   FETCH NEXT FROM db_cursor INTO @NodeName 
END  
 
CLOSE db_cursor 
DEALLOCATE db_cursor
 
SELECT DISTINCT output as CurrentSPNRegisterStatus INTO #spnListCurrent FROM #spnLIst
 
TRUNCATE TABLE #spnLIst
 
-- GET Port Number 
DECLARE @PortNumber varchar(10) 
SELECT @PortNumber = cast(local_tcp_port as varchar(10))
FROM sys.dm_exec_connections WHERE session_id = @@SPID
 
-- GET FQDN
DECLARE @Domain NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT
 
INSERT #spnLIst
SELECT 'MSSQLSvc/' + cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(64)) + ':' + @PortNumber
UNION ALL
SELECT 'MSSQLSvc/' + cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(64)) + '.' + @Domain  + ':' + @PortNumber
UNION ALL
SELECT 'MSSQLSvc/' + cast(SERVERPROPERTY('MachineName') as varchar(64)) + ':' + @PortNumber
UNION ALL
SELECT 'MSSQLSvc/' + cast(SERVERPROPERTY('MachineName') as varchar(64)) + '.' + @Domain + ':' + @PortNumber
 
-- If this serve is clusterd, need to check for all Physical nodes
IF SERVERPROPERTY('IsClustered') = 1
BEGIN
 
   INSERT #spnLIst
   SELECT 'MSSQLSvc/' + NodeName + ':' + @PortNumber
   FROM fn_virtualservernodes()
 
   INSERT #spnLIst
   SELECT 'MSSQLSvc/' + NodeName + '.' + @Domain  + ':' + @PortNumber
   FROM fn_virtualservernodes() 
 
END
 
IF NOT EXISTS(SELECT CurrentSPNRegisterStatus FROM #spnListCurrent)
   SELECT 'NO SPN has been registered' as CurrentSPNRegisterStatus
ELSE
   SELECT CurrentSPNRegisterStatus FROM #spnListCurrent
 
SELECT 
   CASE 
      WHEN A.CurrentSPNRegisterStatus is NULL THEN '*Missing SPN - See SPNGenerateCommandLine'
      ELSE A.CurrentSPNRegisterStatus END AS 'CurrentSPNRegisterStatus', 
   CASE 
      WHEN B.output IS NULL THEN '*** Review for Remove or you have multiple instance ***'
   ELSE B.output end as SuggestSPNList,
   CASE 
      WHEN B.output is null THEN
          'SETSPN -D ' + A.CurrentSPNRegisterStatus + ' ' + @DBEngineLogin
   ELSE 'SETSPN -S ' + output + ' ' + @DBEngineLogin END as SPNGenerateCommandLine
FROM #spnListCurrent A 
FULL OUTER JOIN #spnLIst B on REPLACE(A.CurrentSPNRegisterStatus,CHAR(9),'') = B.output
WHERE CurrentSPNRegisterStatus is NULL OR B.output IS NULL
 
IF @@ROWCOUNT = 0
 SELECT 'All SPN has been registered correctly. If you are running for AG Group, this script does not check so please check manually' as SPNStatus
 
DROP TABLE #spnResult
DROP TABLE #spnLIst
DROP TABLE #spnListCurrent
GO

Example Result 1 – Good Case

This is a sample result for a standalone server with a single instance running. As you can see two SPNs have been registered currently and nothing new is suggested, so the SPNStatus is good.

SPNs have been registered currently and nothing new is suggested

Example Result 2 - Missing SPN on a Standalone Server

This is sample result that you will get if the SPN has not been registered. You can just take the code from the third column “SPNGenerateCommandLine” and run the T-SQL (if you have the correct permissions to register) and it will register the SPN.

Example Result 3 - Wrong SPN Registered (Missing SQLPorts)

Here is an example of the wrong SPN being registered. As you can see, the SPN has been registered without a SQL port like 1433, so in this case the script will generate “SETSPN – D” to remove the existing SPN and also generate another SPN script to register the SPN.

Example Result 4 - Cluster Servers

Don’t be surprised to see results like below if you are running on a clustered node. We found it is much better to register the actual cluster node to reduce errors in the event log, so this script will also check the physical node names and check if those are registered and if not it will generate the recommended scripts. I will leave up to you how you want to use this information. (Note: I hid a bunch of the sensitive information from my machine in the below image.)

SPN being registered for a Clustered Server

Summary

This version of the script is meant and tested for SQL Server 2008 or later and this script is also meant for most general needs. Special cases like connecting SQL Server via a different CNAME or different paths like F5, AlwaysOn Listener, etc. you will need to manually handle these items.

Next Steps

Enhance the script to handle for an AlwaysOn Listener
Add detection for Dynamic Ports

Resources

Community

Subscribe