# ==============================================================================
# Update-SQLServerCertificate.ps1
#
# Post-Renewal Task script for Certify The Web / Let's Encrypt
#
# What it does:
# 1. Pulls certificate details from the Certify $result object
# 2. Auto-discovers ALL SQL Server instances via the registry
# 3. For each instance:
# a. Writes the thumbprint as REG_SZ to:
# HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\
# <InstanceKey>\MSSQLServer\SuperSocketNetLib\Certificate
# (confirmed from live SSCM export — this is exactly where SSCM reads it)
# b. Grants Read on the private key to the SQL service account
# c. Stops the SQL Agent service (if running) before restarting SQL Server
# d. Restarts the SQL Server service (only if StartMode = Auto)
# e. Restarts the SQL Agent service (only if it was running and StartMode = Auto)
# 4. Writes a timestamped audit log
#
# Usage in Certify The Web:
# Managed Certificate > Deployment > Add Deployment Task > Run PowerShell Script
# Script: C:\Scripts\Update-SQLServerCertificate.ps1
#
# KEY REGISTRY FACT (confirmed from live SSCM export):
# Certificate thumbprint lives at:
# HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceKey>\
# MSSQLServer\SuperSocketNetLib\Certificate
# NOT under the \Ssl subkey — that path does not exist in a default installation.
# Thumbprint must be lowercase, no spaces, no leading/trailing characters.
#
# SQL AGENT NOTE:
# SQL Agent is a dependent service of SQL Server. If SQL Server is restarted
# without stopping Agent first, SCM may throw errors. This script:
# - Stops Agent before restarting SQL Server (if Agent was running)
# - Restarts Agent after SQL Server is back up (only if it was running before)
# Agent service names:
# Default instance : SQLSERVERAGENT
# Named instance : SQLAgent$<InstanceName>
# ==============================================================================
param($result)
# --- OPTIONAL CONFIGURATION ---------------------------------------------------
$LogFile = "C:\Logs\SQLCertRenewal.log"
$SkipRestart = $false # Set $true to test without bouncing SQL services
# ------------------------------------------------------------------------------
# ==============================================================================
# LOGGING
# ==============================================================================
function Write-Log {
param([string]$Message, [string]$Level = "INFO")
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
$entry = "[$timestamp] [$Level] $Message"
Write-Host $entry
$logDir = Split-Path $LogFile -Parent
if (-not (Test-Path $logDir)) { New-Item -ItemType Directory -Path $logDir -Force | Out-Null }
Add-Content -LiteralPath $LogFile -Value $entry
}
# ==============================================================================
# STEP 0 — Validate Certify $result object
# ==============================================================================
Write-Log "============================================================"
Write-Log "SQL Server Certificate Renewal Script Started"
$RawThumbprint = $result.ManagedItem.CertificateThumbprintHash
$PfxPath = $result.ManagedItem.CertificatePath
$PrimaryDomain = $result.ManagedItem.RequestConfig.PrimaryDomain
if (-not $RawThumbprint) {
Write-Log "ERROR: CertificateThumbprintHash is empty. Cannot proceed." -Level "ERROR"
exit 1
}
# Sanitise the thumbprint:
# - Strip everything that is not a hex character (removes spaces, dashes,
# hidden Unicode, zero-width spaces, BOM characters etc.)
# - Force lowercase — confirmed from live SSCM registry export:
# SSCM writes the thumbprint as lowercase, SQL Server loads it correctly.
$NewThumbprint = ($RawThumbprint -replace '[^a-fA-F0-9]', '').ToLower()
Write-Log "Certify Renewal Successful"
Write-Log "Primary Domain : $PrimaryDomain"
Write-Log "PFX Path : $PfxPath"
Write-Log "Raw Thumbprint : $RawThumbprint"
Write-Log "Clean Thumbprint: $NewThumbprint (lowercase, no spaces)"
Write-Log "Previous Thumb : $($result.ManagedItem.CertificatePreviousThumbprintHash)"
# Load the cert from LocalMachine\My — the store comparison is case-insensitive
$cert = Get-ChildItem -Path "Cert:\LocalMachine\My" |
Where-Object { $_.Thumbprint -ieq $NewThumbprint } |
Select-Object -First 1
if (-not $cert) {
Write-Log "ERROR: Certificate '$NewThumbprint' not found in LocalMachine\My store." -Level "ERROR"
exit 1
}
Write-Log "Certificate found: Subject=$($cert.Subject) | Expires=$($cert.NotAfter)"
# Inform if SAN cert — these work fine in SQL Server but may not show in SSCM dropdown
$san = $cert.Extensions | Where-Object { $_.Oid.FriendlyName -eq "Subject Alternative Name" }
if ($san) {
Write-Log "INFO: Certificate has Subject Alternative Names (SAN). SQL Server will use it correctly via registry, but it may not appear in the SSCM certificate dropdown — this is expected." -Level "INFO"
}
# ==============================================================================
# STEP 1 — Discover SQL instances via registry (authoritative source)
# ==============================================================================
Write-Log "------------------------------------------------------------"
Write-Log "Discovering SQL Server instances..."
$regInstanceRoot = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
if (-not (Test-Path $regInstanceRoot)) {
Write-Log "ERROR: Registry path not found: $regInstanceRoot" -Level "ERROR"
Write-Log "ERROR: No SQL Server instances found on this machine." -Level "ERROR"
exit 1
}
# Each value under Instance Names\SQL is: InstanceName -> InstanceKey
# e.g. MSSQLSERVER -> MSSQL15.MSSQLSERVER
# CASEMANAGER -> MSSQL15.CASEMANAGER
$instanceMap = @{}
$regProps = Get-ItemProperty -Path $regInstanceRoot
$regProps.PSObject.Properties |
Where-Object { $_.Name -notmatch "^PS" } |
ForEach-Object { $instanceMap[$_.Name] = $_.Value }
if ($instanceMap.Count -eq 0) {
Write-Log "ERROR: No SQL Server instances found in registry." -Level "ERROR"
exit 1
}
Write-Log "Instances discovered ($($instanceMap.Count)): $($instanceMap.Keys -join ', ')"
foreach ($kv in $instanceMap.GetEnumerator()) {
Write-Log " $($kv.Key) -> $($kv.Value)"
}
# ==============================================================================
# HELPERS
# ==============================================================================
function Get-SqlServiceInfo {
param([string]$InstanceName)
# Default instance service name is MSSQLSERVER; named instances are MSSQL$<name>
# Default agent service name is SQLSERVERAGENT; named instances are SQLAgent$<name>
$serviceName = if ($InstanceName -eq "MSSQLSERVER") { "MSSQLSERVER" } else { "MSSQL`$$InstanceName" }
$agentName = if ($InstanceName -eq "MSSQLSERVER") { "SQLSERVERAGENT" } else { "SQLAgent`$$InstanceName" }
$svc = Get-WmiObject -Class Win32_Service -Filter "Name='$serviceName'" -ErrorAction SilentlyContinue
$agentSvc = Get-WmiObject -Class Win32_Service -Filter "Name='$agentName'" -ErrorAction SilentlyContinue
if ($svc) {
Write-Log " Service : $serviceName | Account : $($svc.StartName) | StartMode : $($svc.StartMode)"
if ($agentSvc) {
Write-Log " Agent : $agentName | StartMode : $($agentSvc.StartMode) | State : $($agentSvc.State)"
} else {
Write-Log " Agent : $agentName not found (may not be installed)" -Level "WARN"
}
return @{
ServiceName = $serviceName
ServiceAccount = $svc.StartName
StartMode = $svc.StartMode # "Auto", "Manual", "Disabled"
AgentName = $agentName
AgentStartMode = $agentSvc?.StartMode
AgentRunning = ($agentSvc?.State -eq "Running")
}
}
Write-Log " WARNING: Service '$serviceName' not found in SCM." -Level "WARN"
return $null
}
function Get-SqlNetLibRegPath {
<#
.SYNOPSIS
Returns the SuperSocketNetLib registry key path for a SQL instance.
This is where SSCM reads and writes the Certificate thumbprint value.
.NOTES
Correct path (confirmed from live SSCM registry export):
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceKey>\
MSSQLServer\SuperSocketNetLib
The Certificate value sits DIRECTLY under SuperSocketNetLib.
There is NO \Ssl subkey in a default SQL Server installation.
#>
param([string]$InstanceKey)
$path = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$InstanceKey\MSSQLServer\SuperSocketNetLib"
if (Test-Path $path) {
Write-Log " Registry : $path"
return $path
}
Write-Log " WARNING: Registry path not found: $path" -Level "WARN"
return $null
}
function Grant-PrivateKeyPermission {
param(
[System.Security.Cryptography.X509Certificates.X509Certificate2]$Cert,
[string]$ServiceAccount
)
Write-Log " Granting private key Read to: $ServiceAccount"
# Try CNG key first (used by modern Let's Encrypt certs)
try {
$rsaKey = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($Cert)
if ($rsaKey -is [System.Security.Cryptography.RSACng]) {
$keyName = $rsaKey.Key.UniqueName
$keyFile = @(
"$env:ProgramData\Microsoft\Crypto\Keys\$keyName",
"$env:ProgramData\Microsoft\Crypto\RSA\MachineKeys\$keyName"
) | Where-Object { Test-Path $_ } | Select-Object -First 1
if (-not $keyFile) { throw "CNG key file not found for UniqueName: $keyName" }
Write-Log " CNG key file : $keyFile"
$acl = Get-Acl -Path $keyFile
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule($ServiceAccount, "Read", "Allow")
$acl.AddAccessRule($rule)
Set-Acl -Path $keyFile -AclObject $acl
Write-Log " CNG permission granted."
return
}
} catch {
Write-Log " CNG key attempt failed, trying CAPI: $_" -Level "WARN"
}
# Fallback: CAPI key (legacy)
try {
$keyContainer = $Cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName
$keyPath = "$env:ProgramData\Microsoft\Crypto\RSA\MachineKeys\$keyContainer"
if (-not (Test-Path $keyPath)) { throw "CAPI key file not found: $keyPath" }
Write-Log " CAPI key file: $keyPath"
$acl = Get-Acl -Path $keyPath
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule($ServiceAccount, "Read", "Allow")
$acl.AddAccessRule($rule)
Set-Acl -Path $keyPath -AclObject $acl
Write-Log " CAPI permission granted."
} catch {
throw "Could not grant private key permission (tried CNG and CAPI): $_"
}
}
function Restart-SqlService {
# Handles stop/start/verify for a single service with logging
param(
[string]$ServiceName,
[string]$Label = "SQL Server"
)
Write-Log " Stopping $Label : $ServiceName"
Stop-Service -Name $ServiceName -Force -ErrorAction Stop
Start-Sleep -Seconds 3
Write-Log " Starting $Label : $ServiceName"
Start-Service -Name $ServiceName -ErrorAction Stop
Start-Sleep -Seconds 5
$status = (Get-Service -Name $ServiceName).Status
if ($status -ne "Running") {
throw "$Label did not reach Running state after restart. Status: $status"
}
Write-Log " $Label running." -Level "OK"
}
# ==============================================================================
# STEP 2 — Process each SQL instance
# ==============================================================================
$successCount = 0
$failCount = 0
foreach ($instanceName in $instanceMap.Keys) {
$instanceKey = $instanceMap[$instanceName]
Write-Log "------------------------------------------------------------"
Write-Log "Processing instance : $instanceName (key: $instanceKey)"
try {
# ---- Service info -------------------------------------------------------
$svcInfo = Get-SqlServiceInfo -InstanceName $instanceName
if (-not $svcInfo) {
Write-Log " Skipping — service not found in SCM." -Level "WARN"
$failCount++
continue
}
# ---- Registry path ------------------------------------------------------
# Path: ...\<InstanceKey>\MSSQLServer\SuperSocketNetLib
# Value: Certificate (directly under SuperSocketNetLib — NOT under \Ssl)
$regPath = Get-SqlNetLibRegPath -InstanceKey $instanceKey
if (-not $regPath) {
Write-Log " Skipping — SuperSocketNetLib registry path not found." -Level "WARN"
$failCount++
continue
}
$oldThumb = (Get-ItemProperty -Path $regPath -Name "Certificate" -ErrorAction SilentlyContinue).Certificate
Write-Log " Current thumbprint : '$oldThumb'"
Write-Log " New thumbprint : '$NewThumbprint'"
# ---- Write thumbprint to registry as REG_SZ -----------------------------
Set-ItemProperty -Path $regPath -Name "Certificate" -Value $NewThumbprint -ErrorAction Stop
Write-Log " Registry write: done"
# Confirm what is now in the registry
$writtenThumb = (Get-ItemProperty -Path $regPath -Name "Certificate" -ErrorAction SilentlyContinue).Certificate
Write-Log " Verified registry : '$writtenThumb'"
if ($writtenThumb -ne $NewThumbprint) {
throw "Registry verify failed — expected '$NewThumbprint' but found '$writtenThumb'"
}
# ---- Private key permission ---------------------------------------------
Grant-PrivateKeyPermission -Cert $cert -ServiceAccount $svcInfo.ServiceAccount
# ---- Service restart (Auto-start services only) -------------------------
if ($SkipRestart) {
Write-Log " SkipRestart=true — skipping all service restarts." -Level "WARN"
} elseif ($svcInfo.StartMode -ne "Auto") {
Write-Log " Skipping restart — SQL StartMode is '$($svcInfo.StartMode)' (only Auto services are restarted)." -Level "WARN"
} else {
# Stop Agent first if running — it is a dependent service of SQL Server
# and stopping SQL Server without stopping Agent first can cause SCM errors
if ($svcInfo.AgentRunning) {
Write-Log " SQL Agent is running — stopping before SQL Server restart..."
Stop-Service -Name $svcInfo.AgentName -Force -ErrorAction SilentlyContinue
Start-Sleep -Seconds 3
Write-Log " SQL Agent stopped."
}
# Restart SQL Server
Restart-SqlService -ServiceName $svcInfo.ServiceName -Label "SQL Server"
# Restart Agent only if it was running before — don't start an agent
# that was intentionally stopped or is set to Manual/Disabled
if ($svcInfo.AgentRunning -and $svcInfo.AgentStartMode -eq "Auto") {
Restart-SqlService -ServiceName $svcInfo.AgentName -Label "SQL Agent"
} elseif ($svcInfo.AgentRunning) {
Write-Log " SQL Agent was running but StartMode is '$($svcInfo.AgentStartMode)' — not restarting." -Level "WARN"
} else {
Write-Log " SQL Agent was not running before restart — skipping agent start."
}
}
Write-Log " Instance '$instanceName' complete." -Level "OK"
$successCount++
} catch {
Write-Log " ERROR processing '$instanceName': $_" -Level "ERROR"
# Pull recent SQL errors from Application event log to aid diagnosis
try {
$events = Get-EventLog -LogName Application -Source "MSSQL*" -Newest 3 -ErrorAction SilentlyContinue |
Where-Object { $_.EntryType -eq "Error" }
if ($events) {
Write-Log " Recent SQL Application event errors:" -Level "ERROR"
$events | ForEach-Object {
Write-Log " [$($_.TimeGenerated)] $($_.Message.Split("`n")[0])" -Level "ERROR"
}
}
} catch { }
$failCount++
}
}
# ==============================================================================
# SUMMARY
# ==============================================================================
Write-Log "------------------------------------------------------------"
Write-Log "Summary : Success=$successCount | Failed=$failCount"
Write-Log "Thumbprint : $NewThumbprint"
Write-Log "Cert expiry : $($cert.NotAfter)"
Write-Log "============================================================"
exit $(if ($failCount -gt 0) { 1 } else { 0 })
Loading...