SPN überprüfen

Details
Kategorie: T-SQL 
---- Script immer auf einem anderen Server als dem Zielserver ausführen!!


EXEC sp_configure 'show advanced option', '1';  
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
RECONFIGURE;


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
EXEC sp_configure 'show advanced option', '1';  
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell', 0;  
GO  
EXEC sp_configure 'show advanced option', '0';  
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced option', '1';  
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell', 0;  
GO  
RECONFIGURE;

Main Menu