#Requires -Version 5.1
<#
    JbTecWiz Support Centre -- generated fix script

    Fault : Kerberos failures -- clock skew, SPN duplicates and KDC errors
    Fix   : Resolve the service principal name
    Source: https://jbtecwiz.com/support/srv-id-kerberos-skew

    Run as     : Elevated PowerShell
    Expect     : 40 minutes
    Risk       : high
    Reversible : yes

    WHEN THIS IS THE RIGHT FIX
    Principal unknown, duplicate SPN, or a silent fallback to NTLM.

    HOW TO UNDO IT
    setspn -D and -S can restore the previous registrations, which should be
    recorded before changing.

    This script walks the fix one step at a time and asks before each
    one. Steps with no command are things you do yourself -- it prints
    those and waits. Run with -DryRun to print without executing.

    --------------------------------------------------------------------
    NO WARRANTY - USE AT YOUR OWN RISK

    This script is provided by JbTecWiz as-is and with no warranty of any
    kind, express or implied. You run it entirely at your own risk.

    JbTecWiz accepts no liability for any loss or damage arising from its
    use, including but not limited to data loss, downtime, or configuration
    changes that turn out to be wrong for your system.

    You are responsible for reading this script before running it, for
    satisfying yourself that it suits the machine in front of you, and for
    having a working backup first. Some steps cannot be undone.
    --------------------------------------------------------------------
#>

[CmdletBinding()]
param(
    # Print every step and command without running anything.
    [switch]$DryRun,
    # Do not ask before each step. Read the script first if you use this.
    [switch]$Unattended
)

$ErrorActionPreference = 'Stop'
$script:Failed = 0

function Write-Rule { param([string]$Text)
    Write-Host ''
    Write-Host ('-' * 70) -ForegroundColor DarkGray
    if ($Text) { Write-Host $Text -ForegroundColor Cyan }
}

function Show-Prose { param([string]$Text, [string]$Colour = "Gray")
    if (-not $Text) { return }
    $words = $Text -split "\s+"; $line = "  "
    foreach ($w in $words) {
        if (($line.Length + $w.Length) -gt 74) { Write-Host $line -ForegroundColor $Colour; $line = "  " }
        $line += "$w "
    }
    if ($line.Trim()) { Write-Host $line -ForegroundColor $Colour }
}

function Invoke-Step {
    param(
        [int]$Number, [string]$Do, [string]$Why,
        [scriptblock]$Command, [switch]$Manual, [string]$Shell = "powershell"
    )
    Write-Rule "  Step $Number of 6"
    Show-Prose $Do "White"
    if ($Why) { Write-Host ""; Show-Prose $Why "DarkGray" }

    if ($Manual) {
        Write-Host ''
        Write-Host '  -> Do this yourself, then press Enter to carry on.' -ForegroundColor Yellow
        if (-not $Unattended -and -not $DryRun) { [void](Read-Host) }
        return
    }

    Write-Host ''
    foreach ($l in ($Command.ToString().Trim() -split "`n")) {
        Write-Host ("    " + $l.Trim()) -ForegroundColor Green
    }
    Write-Host ''

    if ($DryRun) { Write-Host "  (dry run -- not executed)" -ForegroundColor DarkGray; return }
    if (-not $Unattended) {
        $a = Read-Host "  Run this step? [Y]es / [S]kip / [Q]uit"
        if ($a -match "^[Qq]") { Write-Host "  Stopped at your request."; exit 0 }
        if ($a -match "^[Ss]") { Write-Host "  Skipped." -ForegroundColor DarkGray; return }
    }
    try { & $Command }
    catch {
        $script:Failed++
        Write-Host ("  Step $Number failed: " + $_.Exception.Message) -ForegroundColor Red
        Show-Prose "The rest of the fix may depend on this. Read the write-up before carrying on." "Red"
        if (-not $Unattended) {
            $c = Read-Host "  Carry on anyway? [y/N]"
            if ($c -notmatch "^[Yy]") { exit 1 }
        }
    }
}

Write-Rule
Write-Host '  Kerberos failures -- clock skew, SPN duplicates and KDC errors' -ForegroundColor White
Write-Host '  Resolve the service principal name' -ForegroundColor Cyan
Write-Host ''
Write-Host '  Risk: high   Reversible   40 minutes'
Write-Host ''
Show-Prose 'No warranty. Use at your own risk - JbTecWiz accepts no liability. Read it before you run it, and have a backup.' 'DarkYellow'
Write-Rule

if (-not $Unattended -and -not $DryRun) {
    $go = Read-Host '  Ready? [y/N]'
    if ($go -notmatch "^[Yy]") { Write-Host "  Nothing was changed."; exit 0 }
}

Invoke-Step -Number 1 -Do 'Look for duplicates across the whole forest. A duplicate breaks Kerberos for both accounts, not just one.' -Why 'A duplicate SPN means the KDC cannot decide which account to issue a ticket for, so it issues none. This is the cause behind a large share of "Kerberos silently falls back to NTLM" reports.' -Command {
    setspn -X -F
}

Invoke-Step -Number 2 -Do 'See what is registered for the account in question.' -Command {
    setspn -L DOMAIN\svc_sql
    Get-ADUser svc_sql -Properties ServicePrincipalNames | Select-Object -ExpandProperty ServicePrincipalNames
}

Invoke-Step -Number 3 -Do 'Remove the wrong registration rather than adding another.' -Command {
    setspn -D MSSQLSvc/sql01.example.local:1433 DOMAIN\wrong_account
}

Invoke-Step -Number 4 -Do 'Add it to the correct account, in both the short and fully qualified forms.' -Why '-S checks for a duplicate before adding, where -A does not. Using -A is how most duplicate SPNs get created in the first place.' -Command {
    setspn -S MSSQLSvc/sql01.example.local:1433 DOMAIN\svc_sql
    setspn -S MSSQLSvc/sql01:1433 DOMAIN\svc_sql
}

Invoke-Step -Number 5 -Do 'Purge the client''s ticket cache and test again -- an old ticket will keep failing after the fix.' -Command {
    klist purge
    klist purge -li 0x3e7
    klist
}

Invoke-Step -Number 6 -Do 'Consider a group managed service account, which registers and rotates its own SPNs.' -Command {
    New-ADServiceAccount -Name gmsa_sql -DNSHostName sql01.example.local -ServicePrincipalNames 'MSSQLSvc/sql01.example.local:1433'
}

Write-Rule "  Confirm it worked"
Show-Prose 'setspn -X reports no duplicates and the client receives a Kerberos ticket for the service.' 'White'
Write-Host ''
if (-not $DryRun) {
    setspn -X -F
    klist | Select-String 'Server:'
}

Write-Rule
if ($script:Failed -gt 0) {
    Write-Host ("  Finished with " + $script:Failed + " failed step(s).") -ForegroundColor Yellow
    Show-Prose 'Read the full write-up at https://jbtecwiz.com/support/srv-id-kerberos-skew' 'Yellow'
} else {
    Write-Host '  Finished.' -ForegroundColor Green
}
Write-Host ''
Show-Prose 'To undo: setspn -D and -S can restore the previous registrations, which should be recorded before changing.' 'DarkGray'
Write-Rule