Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to Check and Auto Generate SPNs for SQL Server


By:   |   Last Updated: 2017-05-08   |   Comments (5)   |   Related Tips: 1 | 2 | 3 | More > 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


Last Updated: 2017-05-08


get scripts

next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, May 08, 2017 - 6:12:51 PM - Kun Lee Back To Top

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 08, 2017 - 3:13:09 PM - Joăo Polisel Back To Top

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 08, 2017 - 12:01:47 PM - Greg Robidoux Back To Top

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 08, 2017 - 11:29:50 AM - Kun Lee Back To Top

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 08, 2017 - 4:57:32 AM - Thomas Franz Back To Top

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.

 


Learn more about SQL Server tools