{"id":9464,"date":"2026-03-04T09:38:04","date_gmt":"2026-03-04T09:38:04","guid":{"rendered":"https:\/\/pariswells.com\/blog\/?p=9464"},"modified":"2026-03-04T09:38:05","modified_gmt":"2026-03-04T09:38:05","slug":"certify-the-web-lets-encrypt-powershell-to-update-certificate-on-sql-server-bindings","status":"publish","type":"post","link":"https:\/\/pariswells.com\/blog\/research\/certify-the-web-lets-encrypt-powershell-to-update-certificate-on-sql-server-bindings","title":{"rendered":"Certify the Web \\ Lets Encrypt Powershell to Update Certificate on SQL Server Bindings"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code class=\"\"># ==============================================================================\n# Update-SQLServerCertificate.ps1\n#\n# Post-Renewal Task script for Certify The Web \/ Let's Encrypt\n#\n# What it does:\n#   1. Pulls certificate details from the Certify $result object\n#   2. Auto-discovers ALL SQL Server instances via the registry\n#   3. For each instance:\n#      a. Writes the thumbprint as REG_SZ to:\n#           HKLM\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\\n#             &lt;InstanceKey>\\MSSQLServer\\SuperSocketNetLib\\Certificate\n#         (confirmed from live SSCM export \u2014 this is exactly where SSCM reads it)\n#      b. Grants Read on the private key to the SQL service account\n#      c. Restarts the service (only if StartMode = Auto)\n#   4. Writes a timestamped audit log\n#\n# Usage in Certify The Web:\n#   Managed Certificate > Deployment > Add Deployment Task > Run PowerShell Script\n#   Script: C:\\Scripts\\Update-SQLServerCertificate.ps1\n#\n# KEY REGISTRY FACT (confirmed from live SSCM export):\n#   Certificate thumbprint lives at:\n#     HKLM\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\&lt;InstanceKey>\\\n#       MSSQLServer\\SuperSocketNetLib\\Certificate\n#   NOT under the \\Ssl subkey \u2014 that path does not exist in a default installation.\n#   Thumbprint must be lowercase, no spaces, no leading\/trailing characters.\n# ==============================================================================\n\nparam($result)\n\n# --- OPTIONAL CONFIGURATION ---------------------------------------------------\n$LogFile     = \"C:\\Logs\\SQLCertRenewal.log\"\n$SkipRestart = $false          # Set $true to test without bouncing SQL services\n# ------------------------------------------------------------------------------\n\n\n# ==============================================================================\n# LOGGING\n# ==============================================================================\n\nfunction Write-Log {\n    param([string]$Message, [string]$Level = \"INFO\")\n    $timestamp = Get-Date -Format \"yyyy-MM-dd HH:mm:ss\"\n    $entry = \"[$timestamp] [$Level] $Message\"\n    Write-Host $entry\n    $logDir = Split-Path $LogFile -Parent\n    if (-not (Test-Path $logDir)) { New-Item -ItemType Directory -Path $logDir -Force | Out-Null }\n    Add-Content -LiteralPath $LogFile -Value $entry\n}\n\n\n# ==============================================================================\n# STEP 0 \u2014 Validate Certify $result object\n# ==============================================================================\n\nWrite-Log \"============================================================\"\nWrite-Log \"SQL Server Certificate Renewal Script Started\"\n\n\n$RawThumbprint = $result.ManagedItem.CertificateThumbprintHash\n$PfxPath       = $result.ManagedItem.CertificatePath\n$PrimaryDomain = $result.ManagedItem.RequestConfig.PrimaryDomain\n\nif (-not $RawThumbprint) {\n    Write-Log \"ERROR: CertificateThumbprintHash is empty. Cannot proceed.\" -Level \"ERROR\"\n    exit 1\n}\n\n# Sanitise the thumbprint:\n#   - Strip everything that is not a hex character (removes spaces, dashes,\n#     hidden Unicode, zero-width spaces, BOM characters etc.)\n#   - Force lowercase \u2014 confirmed from live SSCM registry export:\n#     SSCM writes the thumbprint as lowercase, SQL Server loads it correctly.\n$NewThumbprint = ($RawThumbprint -replace '[^a-fA-F0-9]', '').ToLower()\n\nWrite-Log \"Certify Renewal Successful\"\nWrite-Log \"Primary Domain  : $PrimaryDomain\"\nWrite-Log \"PFX Path        : $PfxPath\"\nWrite-Log \"Raw Thumbprint  : $RawThumbprint\"\nWrite-Log \"Clean Thumbprint: $NewThumbprint  (lowercase, no spaces)\"\nWrite-Log \"Previous Thumb  : $($result.ManagedItem.CertificatePreviousThumbprintHash)\"\n\n# Load the cert from LocalMachine\\My \u2014 the store comparison is case-insensitive\n$cert = Get-ChildItem -Path \"Cert:\\LocalMachine\\My\" |\n        Where-Object { $_.Thumbprint -ieq $NewThumbprint } |\n        Select-Object -First 1\n\nif (-not $cert) {\n    Write-Log \"ERROR: Certificate '$NewThumbprint' not found in LocalMachine\\My store.\" -Level \"ERROR\"\n    exit 1\n}\n\nWrite-Log \"Certificate found: Subject=$($cert.Subject) | Expires=$($cert.NotAfter)\"\n\n# Inform if SAN cert \u2014 these work fine in SQL Server but may not show in SSCM dropdown\n$san = $cert.Extensions | Where-Object { $_.Oid.FriendlyName -eq \"Subject Alternative Name\" }\nif ($san) {\n    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 \u2014 this is expected.\" -Level \"INFO\"\n}\n\n\n# ==============================================================================\n# STEP 1 \u2014 Discover SQL instances via registry (authoritative source)\n# ==============================================================================\n\nWrite-Log \"------------------------------------------------------------\"\nWrite-Log \"Discovering SQL Server instances...\"\n\n$regInstanceRoot = \"HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL\"\n\nif (-not (Test-Path $regInstanceRoot)) {\n    Write-Log \"ERROR: Registry path not found: $regInstanceRoot\" -Level \"ERROR\"\n    Write-Log \"ERROR: No SQL Server instances found on this machine.\" -Level \"ERROR\"\n    exit 1\n}\n\n# Each value under Instance Names\\SQL is: InstanceName -> InstanceKey\n# e.g.  MSSQLSERVER  -> MSSQL15.MSSQLSERVER\n#       CASEMANAGER  -> MSSQL15.CASEMANAGER\n$instanceMap = @{}\n$regProps = Get-ItemProperty -Path $regInstanceRoot\n$regProps.PSObject.Properties |\n    Where-Object { $_.Name -notmatch \"^PS\" } |\n    ForEach-Object { $instanceMap[$_.Name] = $_.Value }\n\nif ($instanceMap.Count -eq 0) {\n    Write-Log \"ERROR: No SQL Server instances found in registry.\" -Level \"ERROR\"\n    exit 1\n}\n\nWrite-Log \"Instances discovered ($($instanceMap.Count)): $($instanceMap.Keys -join ', ')\"\nforeach ($kv in $instanceMap.GetEnumerator()) {\n    Write-Log \"  $($kv.Key) -> $($kv.Value)\"\n}\n\n\n# ==============================================================================\n# HELPERS\n# ==============================================================================\n\nfunction Get-SqlServiceInfo {\n    param([string]$InstanceName)\n    # Default instance service name is MSSQLSERVER; named instances are MSSQL$&lt;name>\n    $serviceName = if ($InstanceName -eq \"MSSQLSERVER\") { \"MSSQLSERVER\" } else { \"MSSQL`$$InstanceName\" }\n    $svc = Get-WmiObject -Class Win32_Service -Filter \"Name='$serviceName'\" -ErrorAction SilentlyContinue\n    if ($svc) {\n        Write-Log \"  Service   : $serviceName  |  Account : $($svc.StartName)  |  StartMode : $($svc.StartMode)\"\n        return @{\n            ServiceName    = $serviceName\n            ServiceAccount = $svc.StartName\n            StartMode      = $svc.StartMode   # \"Auto\", \"Manual\", \"Disabled\"\n        }\n    }\n    Write-Log \"  WARNING: Service '$serviceName' not found in SCM.\" -Level \"WARN\"\n    return $null\n}\n\n\nfunction Get-SqlNetLibRegPath {\n    &lt;#\n    .SYNOPSIS\n        Returns the SuperSocketNetLib registry key path for a SQL instance.\n        This is where SSCM reads and writes the Certificate thumbprint value.\n\n    .NOTES\n        Correct path (confirmed from live SSCM registry export):\n          HKLM\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\&lt;InstanceKey>\\\n            MSSQLServer\\SuperSocketNetLib\n\n        The Certificate value sits DIRECTLY under SuperSocketNetLib.\n        There is NO \\Ssl subkey in a default SQL Server installation.\n    #>\n    param([string]$InstanceKey)\n    $path = \"HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\$InstanceKey\\MSSQLServer\\SuperSocketNetLib\"\n    if (Test-Path $path) {\n        Write-Log \"  Registry  : $path\"\n        return $path\n    }\n    Write-Log \"  WARNING: Registry path not found: $path\" -Level \"WARN\"\n    return $null\n}\n\n\n\n\nfunction Grant-PrivateKeyPermission {\n    param(\n        [System.Security.Cryptography.X509Certificates.X509Certificate2]$Cert,\n        [string]$ServiceAccount\n    )\n    Write-Log \"  Granting private key Read to: $ServiceAccount\"\n\n    # Try CNG key first (used by modern Let's Encrypt certs)\n    try {\n        $rsaKey = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($Cert)\n        if ($rsaKey -is [System.Security.Cryptography.RSACng]) {\n            $keyName = $rsaKey.Key.UniqueName\n            $keyFile = @(\n                \"$env:ProgramData\\Microsoft\\Crypto\\Keys\\$keyName\",\n                \"$env:ProgramData\\Microsoft\\Crypto\\RSA\\MachineKeys\\$keyName\"\n            ) | Where-Object { Test-Path $_ } | Select-Object -First 1\n\n            if (-not $keyFile) { throw \"CNG key file not found for UniqueName: $keyName\" }\n            Write-Log \"  CNG key file : $keyFile\"\n            $acl  = Get-Acl -Path $keyFile\n            $rule = New-Object System.Security.AccessControl.FileSystemAccessRule($ServiceAccount, \"Read\", \"Allow\")\n            $acl.AddAccessRule($rule)\n            Set-Acl -Path $keyFile -AclObject $acl\n            Write-Log \"  CNG permission granted.\"\n            return\n        }\n    } catch {\n        Write-Log \"  CNG key attempt failed, trying CAPI: $_\" -Level \"WARN\"\n    }\n\n    # Fallback: CAPI key (legacy)\n    try {\n        $keyContainer = $Cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName\n        $keyPath      = \"$env:ProgramData\\Microsoft\\Crypto\\RSA\\MachineKeys\\$keyContainer\"\n        if (-not (Test-Path $keyPath)) { throw \"CAPI key file not found: $keyPath\" }\n        Write-Log \"  CAPI key file: $keyPath\"\n        $acl  = Get-Acl -Path $keyPath\n        $rule = New-Object System.Security.AccessControl.FileSystemAccessRule($ServiceAccount, \"Read\", \"Allow\")\n        $acl.AddAccessRule($rule)\n        Set-Acl -Path $keyPath -AclObject $acl\n        Write-Log \"  CAPI permission granted.\"\n    } catch {\n        throw \"Could not grant private key permission (tried CNG and CAPI): $_\"\n    }\n}\n\n\n# ==============================================================================\n# STEP 2 \u2014 Process each SQL instance\n# ==============================================================================\n\n$successCount = 0\n$failCount    = 0\n\nforeach ($instanceName in $instanceMap.Keys) {\n\n    $instanceKey = $instanceMap[$instanceName]\n\n    Write-Log \"------------------------------------------------------------\"\n    Write-Log \"Processing instance : $instanceName  (key: $instanceKey)\"\n\n    try {\n\n        # ---- Service info -------------------------------------------------------\n        $svcInfo = Get-SqlServiceInfo -InstanceName $instanceName\n        if (-not $svcInfo) {\n            Write-Log \"  Skipping \u2014 service not found in SCM.\" -Level \"WARN\"\n            $failCount++\n            continue\n        }\n\n        # ---- Registry path ------------------------------------------------------\n        # Path: ...\\&lt;InstanceKey>\\MSSQLServer\\SuperSocketNetLib\n        # Value: Certificate  (directly under SuperSocketNetLib \u2014 NOT under \\Ssl)\n        $regPath = Get-SqlNetLibRegPath -InstanceKey $instanceKey\n        if (-not $regPath) {\n            Write-Log \"  Skipping \u2014 SuperSocketNetLib registry path not found.\" -Level \"WARN\"\n            $failCount++\n            continue\n        }\n\n        $oldThumb = (Get-ItemProperty -Path $regPath -Name \"Certificate\" -ErrorAction SilentlyContinue).Certificate\n        Write-Log \"  Current thumbprint : '$oldThumb'\"\n        Write-Log \"  New thumbprint     : '$NewThumbprint'\"\n\n        # ---- Write thumbprint to registry as REG_SZ -----------------------------\n        Set-ItemProperty -Path $regPath -Name \"Certificate\" -Value $NewThumbprint -ErrorAction Stop\n        Write-Log \"  Registry write: done\"\n\n        # Confirm what is now in the registry\n        $writtenThumb = (Get-ItemProperty -Path $regPath -Name \"Certificate\" -ErrorAction SilentlyContinue).Certificate\n        Write-Log \"  Verified registry : '$writtenThumb'\"\n\n        if ($writtenThumb -ne $NewThumbprint) {\n            throw \"Registry verify failed \u2014 expected '$NewThumbprint' but found '$writtenThumb'\"\n        }\n\n        # ---- Private key permission ---------------------------------------------\n        Grant-PrivateKeyPermission -Cert $cert -ServiceAccount $svcInfo.ServiceAccount\n\n        # ---- Service restart (Auto-start services only) -------------------------\n        if ($SkipRestart) {\n            Write-Log \"  SkipRestart=true \u2014 skipping service restart.\" -Level \"WARN\"\n        } elseif ($svcInfo.StartMode -ne \"Auto\") {\n            Write-Log \"  Skipping restart \u2014 StartMode is '$($svcInfo.StartMode)' (only Auto services are restarted).\" -Level \"WARN\"\n        } else {\n            Write-Log \"  Stopping : $($svcInfo.ServiceName)\"\n            Stop-Service -Name $svcInfo.ServiceName -Force -ErrorAction Stop\n            Start-Sleep -Seconds 3\n\n            Write-Log \"  Starting : $($svcInfo.ServiceName)\"\n            Start-Service -Name $svcInfo.ServiceName -ErrorAction Stop\n            Start-Sleep -Seconds 5\n\n            $status = (Get-Service -Name $svcInfo.ServiceName).Status\n            if ($status -ne \"Running\") {\n                throw \"Service did not reach Running state after restart. Status: $status\"\n            }\n            Write-Log \"  Service running.\" -Level \"OK\"\n        }\n\n        Write-Log \"  Instance '$instanceName' complete.\" -Level \"OK\"\n        $successCount++\n\n    } catch {\n        Write-Log \"  ERROR processing '$instanceName': $_\" -Level \"ERROR\"\n\n        # Pull recent SQL errors from Application event log to aid diagnosis\n        try {\n            $events = Get-EventLog -LogName Application -Source \"MSSQL*\" -Newest 3 -ErrorAction SilentlyContinue |\n                      Where-Object { $_.EntryType -eq \"Error\" }\n            if ($events) {\n                Write-Log \"  Recent SQL Application event errors:\" -Level \"ERROR\"\n                $events | ForEach-Object {\n                    Write-Log \"    [$($_.TimeGenerated)] $($_.Message.Split(\"`n\")[0])\" -Level \"ERROR\"\n                }\n            }\n        } catch { }\n\n        $failCount++\n    }\n}\n\n\n# ==============================================================================\n# SUMMARY\n# ==============================================================================\n\nWrite-Log \"------------------------------------------------------------\"\nWrite-Log \"Summary      : Success=$successCount | Failed=$failCount\"\nWrite-Log \"Thumbprint   : $NewThumbprint\"\nWrite-Log \"Cert expiry  : $($cert.NotAfter)\"\nWrite-Log \"============================================================\"\n\nexit $(if ($failCount -gt 0) { 1 } else { 0 })<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-9464","post","type-post","status-publish","format-standard","hentry","category-research"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9464","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/comments?post=9464"}],"version-history":[{"count":1,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9464\/revisions"}],"predecessor-version":[{"id":9465,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9464\/revisions\/9465"}],"wp:attachment":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/media?parent=9464"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/categories?post=9464"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/tags?post=9464"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}