Script to Check and Auto Generate SPNs for SQL Server

By:   |   Comments (6)   |   Related: 1 | 2 | 3 | > Security


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.

SPN has not been registered

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.

wrong SPN being registered

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, June 15, 2020 - 2:12:08 AM - Hany Helmy Back To Top (86003)

Hi,

Just ran "Kerberos Configuration Manager for SQL Server" tool giving me Status of All Good, SPNs registered properly, but your script is giving different result, Missing SPNs, any idea why that is?

Thanks.


Monday, May 8, 2017 - 6:12:51 PM - Kun Lee Back To Top (55585)

João

That is very nice tool. I just downloaded and test it out. Mine is part of automated sql server build script so it is still good for me since it is not GUI (I love script) so that I can run many servers easily but this is very nice tool for me to use when I troubleshoot issues or something else.

BTW, just something to add is, if you have MS SCOM or some other monitoring tool, those also have feature like that too. I didn't mentioned but since you brought up other methods, that is another one to have.

Thank you again for great recommendation!

Kun

 


Monday, May 8, 2017 - 3:13:09 PM - Joăo Polisel Back To Top (55581)

Post and script are great, but I would recommend you to download and run "Kerberos Configuration Manager for SQL Server" from Microsoft Download site. It will not only check for missing SPNs, but also for duplicate SPNs and delegation issues. The tool can fix the issues, or provide you a CMD script to fix.

Cheers,

João


Monday, May 8, 2017 - 12:01:47 PM - Greg Robidoux Back To Top (55573)

I checked the code that displays on screen and removed the extra HTML space characters.

I did not see a tab in the code.

-Greg


Monday, May 8, 2017 - 11:29:50 AM - Kun Lee Back To Top (55572)

Thanks Thomas!

I will follow up on this. In the mean while, could you try the download version from the article "Here is the code. You can also download here." I want to make sure that is good on that

Regards

Kun

 


Monday, May 8, 2017 - 4:57:32 AM - Thomas Franz Back To Top (55557)

Very nice / useful script, but please remove the tab character in the delete commando (SETSPN -D<space><tab>MSSQLSvc\...). Otherwise it would replace it with the first file / directory in my current cmd session, when I copy / paste the statement into cmd.

 















get free sql tips
agree to terms