To automate the frequently occurring SPN errors in our environment, we planned to develop a script which can automatically take care of finding out the missing SPNs and making sure they are fixed for all the SQL Service Instance running.
The script provided below is the result of this development. It is a
PowerShell job and will only work with
SQL Server 2008 R2 and above. A few variable values you need to update before you set this job in place are below. The names are self explanatory. $SendMail is a flag, if it should send an email or not at the end with the execution logs. No email will be sent, if all SPNs are registered.
- $smtpServer
- $MailFrom
- $mailto
- $SQL_INSTANCE (List of instance you need to check this job for SPN errors
- $SendMail
The script will also generate a log file in the default log location of the first instance provided in the list of $SQL_INSTANCE variable. A table in the first instance specified in master database will also hold the date and the SPN commands executed for your tracking purpose. The table name would be AutoSPNCheckLog.
Please use this script at your own risk, although I have tested this thoroughly in my environment. Here is the code. Hope you would like it.
/*################################################################################################################
### THIS JOB SHOULD ONLY BE SETUP WITH AN ACCOUNT WITH PREVILEGES ON ACTIVE DIRECTORY TO REGISTER SPN.
### GET AN ACCOUNT CREATED IF THE SERVICE ACCOUNT DOESN''T HAS THAT PERMISSION.
### USE IT AT YOUR OWN RISK
### Update following variables before deploying in a Customer environment
### 1. $smtpServer
### 2. $MailFrom
### 3. $mailto
### 4. $SQL_INSTANCE (List of instance you need to check this job for SPN errors
################################################################################################################
*/
USE [msdb]
GO
/****** Object: Job [SPN FIX] Script Date: 3/5/2019 7:20:08 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Database Maintenance]] Script Date: 3/5/2019 7:20:08 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Database Maintenance]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Database Maintenance]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AUTO SPN FIX',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Database Maintenance]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [SPN Fix] Script Date: 3/5/2019 7:20:08 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SPN Fix',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'################################################################################################################
### THIS JOB SHOULD ONLY BE SETUP WITH AN ACCOUNT WITH PREVILEGES ON ACTIVE DIRECTORY TO REGISTER SPN.
### GET AN ACCOUNT CREATED IF THE SERVICE ACCOUNT DOESN''T HAS THAT PERMISSION.
### USE IT AT YOUR OWN RISK
### Update following variables before deploying in a Customer environment
### 1. $smtpServer
### 2. $MailFrom
### 3. $mailto
### 4. $SQL_INSTANCE (List of instance you need to check this job for SPN errors
################################################################################################################
$SQL_INSTANCE = @("Inst1","Inst2")
$smtpServer = "MySMTPServer.com"
$MailFrom = "Monitoring@MyDomain.com"
$mailto = "NotificationList@MyDomain.com"
$SendMail = 1
#region Import Modules
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
TRY
{
Add-PSSnapin SqlServerCmdletSnapin100 -errorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -erroraction SilentlyContinue
Import-Module "sqlps" -DISABLENAMECHECKING -ErrorAction SilentlyContinue
import-module failoverclusters -ErrorAction SilentlyContinue
}
catch
{
"`nError Occurred importing modules :" + $_.exception.message| out-file -FilePath $LogLocation -Append
}
try
{
Import-Module ActiveDirectory
}
catch
{
"`nActive Directory powershell module not found. Installing module " | out-file -FilePath $LogLocation
Add-WindowsFeature -Name "RSAT-AD-PowerShell" -ErrorAction Stop -LogPath $LogLocation
Import-Module ActiveDirectory
}
#endregion
#region: Get error log folder path from first instance
$table = new-object system.data.datatable
$ErrorLogQuery = "select substring(cast(value_data as nvarchar(1000)),3,len(cast(value_data as nvarchar(1000)))-11) [Path] from sys.dm_server_registry where value_name = ''SQLArg1''"
$mydata = Invoke-Sqlcmd -Query $ErrorLogQuery -serverinstance $SQL_INSTANCE[0] -MaxCharLength 800000 -QueryTimeout 600
$LogLocation = $mydata.Path
$TodayDate = Get-Date -Format yyyymmdd
$suffix = "SPN_Fix_" + $TodayDate + ".log"
$LogLocation = $LogLocation + $suffix
"`nLog file is being stored at - " + $LogLocation + "
" | out-file -FilePath $LogLocation
$cmd = ""
#endregion
#region: Create SPN Check log table in first instance
$table = new-object system.data.datatable
$LogTableQuery = "if not exists(select name from master.sys.objects where name = ''AutoSPNCheckLog'' and type = ''U'') CREATE TABLE AutoSPNCheckLog(LogTime datetime, LogText nvarchar(4000))"
$mydata = Invoke-Sqlcmd -Query $LogTableQuery -serverinstance $SQL_INSTANCE[0] -MaxCharLength 800000 -QueryTimeout 600
"`nSPN Check log table created on - " + $SQL_INSTANCE[0] + "
" | out-file -FilePath $LogLocation
#endregion
"`nStarting SPN Check and fix @ " + (Get-Date -DisplayHint DateTime).ToString() + "
" | out-file -FilePath $LogLocation
"-----------------------------------------------------
" | out-file -FilePath $LogLocation -Append
#" " | out-file -FilePath $LogLocation -Append
$SendMail = 0
$CommandText = @"
select @@servername[Server], SERVERPROPERTY(''IsClustered'') IsClustered, auth_scheme,net_transport, client_net_address,local_tcp_port from sys.dm_exec_connections where session_id=@@spid and auth_scheme <> ''KERBEROS''
"@
$html = "
SPN_Fix_Report
Auto SPN Fix Report
Please find below SPN fix execution log. Address errors reported if any.
";
$table +="| Server | auth_scheme | net_transport | client_net_address | local_tcp_port | Message | "
$body += @"
"@
$SAcmd = "SELECT service_account FROM sys.dm_server_services where filename like ''%sqlservr.exe%''"
$Portcmd = "select top 1 value_data from sys.dm_server_registry where value_name = ''TcpPort'' and value_data <> ''''"
$table = new-object system.data.datatable
"`r`nInstance identified for SPN Check
" | out-file -FilePath $LogLocation -Append
"-----------------------------------------------------
" | out-file -FilePath $LogLocation -Append
$SQL_INSTANCE + "
" | out-file -FilePath $LogLocation -Append
ForEach ($instance in $SQL_INSTANCE)
{
"`r`n
Servicing instance $instance
" | out-file -FilePath $LogLocation -Append
"-----------------------------------------------------
" | out-file -FilePath $LogLocation -Append
try
{
#Get Service account
$SAccount = Invoke-Sqlcmd -Query $SAcmd -serverinstance $instance -MaxCharLength 800000 -QueryTimeout 600
$ServiceAccount = $SAccount.service_account
"`nService Account for instance $instance is $ServiceAccount
" | out-file -FilePath $LogLocation -Append
#Get Port number
$PortNumber = Invoke-Sqlcmd -Query $Portcmd -serverinstance $instance -MaxCharLength 800000 -QueryTimeout 600
$SQLPort = $PortNumber.value_data
"`nPort number for instance $instance is $SQLPort
" | out-file -FilePath $LogLocation -Append
$var = setspn -L $ServiceAccount
$status = 0
ForEach ($entry in $var) #Scan through all registered SPNs with the service account
{
if(!$instance.Contains("\")) #Default Instance
{
$SPN1 = "MSSQLSvc/$instance.$env:USERDNSDOMAIN`:$SQLPort"
$SPN2 = "MSSQLSvc/$instance.$env:USERDNSDOMAIN"
}
elseif($instance.Contains("\")) #Named Instance
{
$part1 = $instance.Substring(0, $instance.IndexOf("\"))
$part2 = $instance.Substring($instance.IndexOf("\")+1, $instance.Length-$instance.IndexOf("\")-1)
$SPN1 = "MSSQLSvc/$part1.$env:USERDNSDOMAIN`:$SQLPort"
$SPN2 = "MSSQLSvc/$part1.$env:USERDNSDOMAIN`:$part2"
}
if($entry.Trim() -eq $SPN1)
{
$status +=1
"`nFound below entry for instance $instance.`n$entry
" | out-file -FilePath $LogLocation -Append
}
if($entry.Trim() -eq $SPN2)
{
$status +=1
"`nFound below entry for instance $instance.`n$entry
" | out-file -FilePath $LogLocation -Append
}
}
if($status -ne 2) #SPNs are not registered
{
if ($status -eq 0)
{
"`nBoth SPNs are found missing. Registering SPNs.
" | out-file -FilePath $LogLocation -Append
$SendMail = 1
}
elseif($status -eq 1)
{
"`nOne of the SPN is found missing. Registering SPN now.
" | out-file -FilePath $LogLocation -Append
$SendMail = 1
}
elseif($status -ge 3)
{
#Send email to me
}
if(!$instance.Contains("\")) #Default Instance
{
#Add Entry to SQL Tables with commands to execute
"`nRegistering SPN for instance : $instance
" | out-file -FilePath $LogLocation -Append
"`nsetspn -A MSSQLSvc/$instance.$env:USERDNSDOMAIN`:$SQLPort $ServiceAccount
" | out-file -FilePath $LogLocation -Append
"`nsetspn -A MSSQLSvc/$instance.$env:USERDNSDOMAIN $ServiceAccount
" | out-file -FilePath $LogLocation -Append
$SPN1 = "MSSQLSvc/" + $instance + "." + $env:USERDNSDOMAIN + ":" + $SQLPort #+ " " + $ServiceAccount
$SPN2 = "MSSQLSvc/" + $instance + "." + $env:USERDNSDOMAIN #+ " " + $ServiceAccount
try
{
$AccountName = $ServiceAccount.Substring($ServiceAccount.IndexOf("\")+1, `
$ServiceAccount.Length-$ServiceAccount.IndexOf("\")-1)
$user = (get-aduser -Identity $AccountName).DistinguishedName
"`nRegistering SPN for instance : $SPN1
" | out-file -FilePath $LogLocation -Append
Set-ADObject -Identity $user -add @{serviceprincipalname=$SPN1}
"`nRegistering SPN for instance : $SPN2
" | out-file -FilePath $LogLocation -Append
Set-ADObject -Identity $user -add @{serviceprincipalname=$SPN2}
"`nSPN Registerion completed successfully for instance : $instance
" | out-file -FilePath $LogLocation -Append
#region: Enter SPN Entry in SPN Log table
$logdate = Get-Date -Format "MM-dd-yyyy hh:mm:ss"
$LogTableQuery = "INSERT INTO AutoSPNCheckLog VALUES(''" + $logdate + "'', ''SPN Updated -> " + $SPN1.Substring(0,$SPN1.Length-1) + " " + $ServiceAccount + "'')"
$mydata = Invoke-Sqlcmd -Query $LogTableQuery -serverinstance $SQL_INSTANCE[0] -MaxCharLength 800000 -QueryTimeout 600
$LogTableQuery = "INSERT INTO AutoSPNCheckLog VALUES(''" + $logdate + "'', ''SPN Updated -> " + $SPN2.Substring(0,$SPN2.Length-1) + " " + $ServiceAccount + "'')"
$mydata = Invoke-Sqlcmd -Query $LogTableQuery -serverinstance $SQL_INSTANCE[0] -MaxCharLength 800000 -QueryTimeout 600
"`nSPN data inserted in LogTableQuery table on instance - " + $SQL_INSTANCE[0] + "
"| out-file -FilePath $LogLocation -Append
}
Catch
{
"`n" + $_.Exception.Message + " Please check and retry after resolving error.
" | out-file -FilePath $LogLocation -Append
$LogTableQuery = "INSERT INTO AutoSPNCheckLog VALUES(''" + $logdate + "'', ''Error Occurred while updating SPN. Error Details -> " + $_.Exception.Message + "'')"
$mydata = Invoke-Sqlcmd -Query $LogTableQuery -serverinstance $SQL_INSTANCE[0] -MaxCharLength 800000 -QueryTimeout 600
}
}
elseif($instance.Contains("\")) #Named Instance
{
"`nRegistering SPN for instance : $instance
" | out-file -FilePath $LogLocation -Append
$part1 = $instance.Substring(0, $instance.IndexOf("\"))
$part2 = $instance.Substring($instance.IndexOf("\")+1, $instance.Length-$instance.IndexOf("\")-1)
"`nsetspn -A MSSQLSvc/$part1.$env:USERDNSDOMAIN`:$SQLPort $ServiceAccount
" | out-file -FilePath $LogLocation -Append
#write-host "MSSQLSvc/" + $env:COMPUTERNAME + "." + $env:USERDNSDOMAIN + ":" + $part2 + " " + $ServiceAccount
"`nsetspn -A MSSQLSvc/$part1.$env:USERDNSDOMAIN`:$part2 $ServiceAccount
" | out-file -FilePath $LogLocation -Append
$SPN1 =''MSSQLSvc/'' + $part1 + "." + $env:USERDNSDOMAIN + ":" + $SQLPort.trim() #+ '' '' + $ServiceAccount
$SPN2 = "MSSQLSvc/" + $part1 + "." + $env:USERDNSDOMAIN + ":" + $part2 #+ " " + $ServiceAccount
try
{
$AccountName = $ServiceAccount.Substring($ServiceAccount.IndexOf("\")+1, `
$ServiceAccount.Length-$ServiceAccount.IndexOf("\")-1)
$user = (get-aduser -Identity $AccountName).DistinguishedName
"`nRegistering SPN for instance : $SPN1
" | out-file -FilePath $LogLocation -Append
Set-ADObject -Identity $user -add @{serviceprincipalname=$SPN1}
"`nRegistering SPN for instance : $SPN2
" | out-file -FilePath $LogLocation -Append
Set-ADObject -Identity $user -add @{serviceprincipalname=$SPN2}
"`nSPN Registerion completed successfully for instance : $instance
" | out-file -FilePath $LogLocation -Append
#region: Enter SPN Entry in SPN Log table
$logdate = Get-Date -Format "MM-dd-yyyy hh:mm:ss"
$LogTableQuery = "INSERT INTO AutoSPNCheckLog VALUES(''" + $logdate + "'', ''SPN Updated -> " + $SPN1.Substring(0,$SPN1.Length-1) + " " + $ServiceAccount + "'')"
$mydata = Invoke-Sqlcmd -Query $LogTableQuery -serverinstance $SQL_INSTANCE[0] -MaxCharLength 800000 -QueryTimeout 600
$LogTableQuery = "INSERT INTO AutoSPNCheckLog VALUES(''" + $logdate + "'', ''SPN Updated -> " + $SPN2.Substring(0,$SPN2.Length-1) + " " + $ServiceAccount + "'')"
$mydata = Invoke-Sqlcmd -Query $LogTableQuery -serverinstance $SQL_INSTANCE[0] -MaxCharLength 800000 -QueryTimeout 600
"`nSPN data inserted in LogTableQuery table on instance - " + $SQL_INSTANCE[0] + "
" | out-file -FilePath $LogLocation -Append
#endregion
}
Catch
{
"`n" + $_.Exception.Message + " Please check and retry after resolving error.
" | out-file -FilePath $LogLocation -Append
$LogTableQuery = "INSERT INTO AutoSPNCheckLog VALUES(''" + $logdate + "'', ''Error Occurred while updating SPN. Error Details -> " + $_.Exception.Message + "'')"
$mydata = Invoke-Sqlcmd -Query $LogTableQuery -serverinstance $SQL_INSTANCE[0] -MaxCharLength 800000 -QueryTimeout 600
}
}
}
}
catch
{
"`nError occured while registering SPNs : " + $_.exception.message + "
" | out-file -FilePath $LogLocation -Append
#Write-Error ("At line number - " + $_.InvocationInfo.ScriptLineNumber | Out-String)
}
}
if($SendMail -eq 1)
{
$LogData = get-content -Path $LogLocation
$html = $html + ""+ $LogData + "
"
# Send email with log attachment.
$datepart = Get-Date -Format g
$MailSubject = "Auto_SPN_Fix_Report " + $datepart.ToString()
Send-MailMessage -From $MailFrom -To $mailto -Subject $MailSubject `
-Body $html -SmtpServer $smtpServer -BodyAsHtml
#endregion
}
#Remove HTML tags from log file
$logdata = ((Get-Content -path $LogLocation -Raw) -replace ''
'','' '')
Set-Content -Path $LogLocation -Value $LogData',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
