SPN & Delegation backup script

I use SPNs quite extensively to allow Reporting Services to talk to databases severs,  Sharepoint etc. It’s a core component of any new server setup I perform. We also employ Kerberos delegation that allows credentials to be passed down from the user through SSRS to the database server. In most service accounts delegation is simply set to Trust this user for delegation to any service (Kerberos only) but we do have a few where specific services are defined.

This is where my script comes in. I built this solution in response to a problem I also created. The best kind eh!

I was working with a sys admin to fault find an anonymous login issue (primarily caused by missing delegation or SPNs when using kerberos auth) In an effort to eliminate suspected issues I asked him to switch the production service account (FIRST MISTAKE! :)) to Trust this user for delegation to any service (Kerberos only) from its specified list of services. When this didn’t work I asked him to switch it back and thats when we both discovered the nice list of 10+ services defined was blank…

After hours of troubleshooting, trawling through requests to add SPNs & delegation we were able to rebuild the list. This was when I decided to build a safety net to prevent this from happening again. A powershell script to grab the info from AD & back it up to csv files (which I then pushed into version control software) was easily the best option. It could be setup as a task & ran every month to keep an up to date record of the SPNs & delegation records on our service accounts should the accounts get deleted or someone makes my mistake again.

The script is pretty well documented but if you have any questions on any part, pop a comment in.

#------------------------------------------------------------------
# AUTHOR:	Craig Porteous
# USE:		Retrieve SPNs & Kerberos delegation values from accounts
# in a specific OU (Service accounts). Back these values up to csv.
#------------------------------------------------------------------

#------------------------------------------------------------------
# Set up & test output location. Start transcript.
#------------------------------------------------------------------

$path = "D:\Powershell Scripts\"
$mailServer = ""
$mailFrom = ""
$mailTo = "craig@craigporteous.com"
$computer = $env:computername

# Test the path parameter. This is a code segment taken from another script 
#where the path is passed in by the user so requires validation. 

If(Test-path $path){
}
Else{
    While (!(Test-path $path)){
        $path = Read-Host "$path does not exist! Please set a valid output location"
        If(Test-path $path){
            if ($path.EndsWith("/")){  
                $path = $path.Substring(0,$path.length-1); 
            }
            Write-output "Output path set to $path"
        }
        Else {
            Write-Host "Output path is not valid!" -ForegroundColor Red;
        }
    }
}
#Define variables & arrays
$path = $path + "AUTO SPN BACKUP" + "\"
#Array to store SPN values
$global:spnarray = @()
#Array to store Delegation values
$global:delarray = @()
 
# Create specific folder for deployment
If (!(Test-Path -Path $path -Erroraction SilentlyContinue )) {
    New-Item -ItemType directory -Path ($path) -force
}
#Transcribe script output to log file
$LogTime = Get-Date -Format "MM-dd-yyyy_hh-mm-ss"
New-Item ($path + "LOG_" + $LogTime + ".log") -type file -force
Start-Transcript -path ($path + "LOG_" + $LogTime + ".log")    

#------------------------------------------------------------------
# Set Search string & connect to AD
#------------------------------------------------------------------
cls
$search = New-Object DirectoryServices.DirectorySearcher([ADSI]“”)
$search.filter = “(servicePrincipalName=*)”
#This is the OU you are searching inside. This will be unique to a company's AD structure
$results = $search.Findall() | ?{ $_.path -like '*OU=BI,OU=SQL Service Accounts,OU=ServiceAccounts,DC=ops,DC=global,DC=ad' }

#------------------------------------------------------------------
# Scan & ouptut results
#------------------------------------------------------------------
foreach($result in $results)
{
	$userEntry = $result.GetDirectoryEntry()
	Write-host "Object Name = " $userEntry.name -backgroundcolor "yellow" -foregroundcolor "black"
	$spnName = $userEntry.name
	Write-host "DN      =      "  $userEntry.distinguishedName
	Write-host "Object Cat. = "  $userEntry.objectCategory
	Write-host "servicePrincipalNames"
	$i=1
	foreach($SPN in $userEntry.servicePrincipalName)
	{
	  	Write-host "SPN(" $i ")   =      " $SPN       
		$details = @{
            Account  	= $spnName.Value
			SPN		    = $SPN
        }
        $Global:spnarray += New-Object PSObject -Property $details
		
		$i+=1
	}
	Write-host ""
	$i=1
	foreach($Delegate in $userEntry."msDS-AllowedToDelegateTo")
	{
	  	Write-host "DEL(" $i ")   =      " $Delegate       
		$details = @{
            Account  	= $spnName.Value
			Delegation  = $Delegate
		}
        $Global:delarray += New-Object PSObject -Property $details
		$i+=1
	}
	Write-host ""
} 
#------------------------------------------------------------------

# Save arrays to CSV files. These can then be backed up or handled by a version control system
$Global:spnarray | export-csv -Path (Join-Path $path SPNList.csv) -NoTypeInformation
	
$Global:Delarray | export-csv -Path (Join-Path $path DelegationList.csv) -NoTypeInformation
	
# If success, build up the email, attachment and text
$font = "<font face=""Verdana"">"
$body = $font
$body = "$font Auto backup Source: $computer"
$body += "<br><br>Local Location: $Path"	
	
send-mailmessage -SmtpServer $mailServer -from $mailFrom -to $mailTo -subject "Auto SPN & Delegation Backup complete on $computer" -BodyAsHtml -Body $body

Write-output ""
Stop-Transcript
#------------------------------------------------------------------

Check out my other posts on PowerShell