SQL Service SPN Auto fix Script and job

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.

1. $smtpServer
2. $MailFrom
3. $mailto
4. $SQL_INSTANCE (List of instance you need to check this job for SPN errors
5. $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 = "

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

How to solve Azure Site Recovery - Error ID 78007 Provider error 95103

While configuring Azure site recovery, one of the most common errors you might receive is Error ID - 78007. If you look at the error, you will find that Microsoft suggests the culprit is WMI or firewall. Error message should be "Connectivity failure- Mobility agent installation failed on the source machine.

In my case, I checked that none of these were issues, as I was able to telnet to port 443 from source to configuration server without issues, and all the exceptions on firewall were in place. Also the File and print sharing was enabled as Microsoft suggests, on my source machine.

If this is the case with you as well, read along and the error should be resolved in no time.

On your source server find and uninstall the Microsoft Azure Site Recovery Mobility Service/Master Target Server service.

While setting up your configuration server, If you have chosen the default installation path, go to C:\ProgramData\ASR\home\svsystems\pushinstallsvc\repository directory on your configuration server. This directory contains the mobility agent installation files for different types of source machine OS, which Microsoft supports.

In my case, the target was a windows machine. So I selected the latest Windows_GA release and copied to my source server. You have to select the release as per your source machine OS. Once copied, double click to start the installation.

You can choose the installation directory at next step. I left it to default and click next.

Installation will proceed and will go through pre-requisites, installation and configurations.

Once the installation is completed, proceed with configuration and provide the Configuration server IP address and the passphrase for your recovery service vault.

If you don't have the passphrase handy, you can use below command to generate passphrase on your configuration server.
cd %ProgramData%\ASR\home\svsystems\bin genpassphrase.exe -v > MobSvc.passphrase

Once completed, go back to the recovery service vault page on Azure portal and restart the replication job.

You should be able to see the replication agent running successfully now.

Hope you find the post helpful.