Public/Test-SqlCertBinding.ps1
|
# ============================================================================= # Script : Public/Test-SqlCertBinding.ps1 # Author : Keith Ramsey # Created : 2026-05-15 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-05-15 Keith Ramsey Initial: migrated from worker Verify stage. # 2026-06-06 Keith Ramsey Removed dbatools dependency (DR-003 retired). # Native registry read is the only path. For # running-instance verification, check the SQL # Server ERRORLOG for "successfully loaded for # encryption" -- registry reports what SQL will use # on the next restart, not what is currently active. # 2026-06-08 Keith Ramsey Expiry check: look up bound cert in # Cert:\LocalMachine\My and return Failed with # ExpiresOn/IsExpired in Data when expired. # 2026-06-08 Keith Ramsey DR-012: add -Node and -Credential for remote # registry verification. Added Steps: blueprint. # 2026-06-08 Keith Ramsey RISK-2: guard ExpiresOn null in expiry detail # string — DR-005 never-throw hardening. # ============================================================================= # Decision Contract: never-throw SqlCert.Result (DR-005); registry-only verify. # ============================================================================= function Test-SqlCertBinding { <# .SYNOPSIS Reports the certificate binding from the registry. .DESCRIPTION Reads the SuperSocketNetLib registry key for each SQL instance and returns the bound thumbprint, ForceEncryption flag, and certificate expiry information. This reflects what SQL Server will use on the next service restart. Returns Status=Failed if the bound certificate has expired -- SQL Server will reject an expired cert on restart. For confirmation that the cert was actually loaded by the running instance, check the SQL Server ERRORLOG for "successfully loaded for encryption". Use -Node to verify registry bindings on one or more remote nodes over PowerShell remoting (WinRM/WS-Man). Each node runs the registry read and cert-store lookup locally inside an Invoke-Command scriptblock. Supply -Credential for workgroup or cross-domain nodes where Kerberos pass-through does not apply. Never throws. .PARAMETER SqlInstance Instance(s) to inspect. Use 'MSSQLSERVER' for the default instance or 'HOST\INSTANCE' for a named one. .PARAMETER Node One or more computers on which to read the registry binding. Defaults to the local machine. Remote nodes are reached over PowerShell remoting (WinRM/WS-Man). .PARAMETER Credential Credential for PSSession authentication. Required for workgroup nodes or cross-domain targets where Kerberos pass-through does not apply. .OUTPUTS SqlCert.Result (Data: per-node/per-instance rows with InstanceName, Thumbprint, ForceEncryption, ExpiresOn, IsExpired, Node). .EXAMPLE Test-SqlCertBinding -SqlInstance 'MSSQLSERVER' .EXAMPLE Test-SqlCertBinding -SqlInstance 'MSSQLSERVER' -Node 'NODE1','NODE2' .EXAMPLE Test-SqlCertBinding -SqlInstance 'MSSQLSERVER' -Node 'NODE1' -Credential $cred .NOTES Steps: 1. For each node in -Node: if local run registry + cert-store logic inline; if remote open Invoke-Command session. 2. In registry read block: resolve instance ID from Instance Names key; read SuperSocketNetLib key. 3. In cert-store block: find cert by thumbprint in Cert:\LocalMachine\My; check NotAfter vs current time. 4. Build per-instance data row: InstanceName, Thumbprint, ForceEncryption, ExpiresOn, IsExpired. 5. Aggregate all rows and failures across all nodes. 6. If any registry read failed, return SqlCert.Result Failed with all collected data. 7. If any cert IsExpired=true, return SqlCert.Result Failed with expiry detail. 8. Else return SqlCert.Result Success with all data rows. #> [CmdletBinding()] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseUsingScopeModifierInNewRunspaces', '', Justification = 'Remote Invoke-Command blocks receive values via param() + -ArgumentList, not closure capture.')] [OutputType('SqlCert.Result')] param( [Parameter(Mandatory)] [string[]] $SqlInstance, [string[]] $Node = @($env:COMPUTERNAME), [PSCredential] $Credential ) $instNames = $SqlInstance | ForEach-Object { $_.Split('\')[-1] } $sb = { param($instNames) foreach ($instName in $instNames) { try { $names = Get-ItemProperty ` 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' ` -ErrorAction Stop $instId = if ($names.PSObject.Properties[$instName]) { $names.$instName } else { $null } if (-not $instId) { throw "Could not resolve instance id for '$instName'" } $key = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instId\MSSQLServer\SuperSocketNetLib" $props = Get-ItemProperty $key -ErrorAction Stop $tp = $props.Certificate $certObj = Get-ChildItem Cert:\LocalMachine\My -ErrorAction SilentlyContinue | Where-Object { $_.Thumbprint -ieq $tp } | Select-Object -First 1 [pscustomobject]@{ InstanceName = $instName Thumbprint = $tp ForceEncryption = $props.ForceEncryption ExpiresOn = if ($certObj) { $certObj.NotAfter } else { $null } IsExpired = [bool]($certObj -and $certObj.NotAfter -lt [datetime]::Now) Error = $null } } catch { [pscustomobject]@{ InstanceName = $instName Thumbprint = $null ForceEncryption = $null ExpiresOn = $null IsExpired = $false Error = $_.Exception.Message } } } } $failed = @() $data = foreach ($n in $Node) { try { $rows = if ($n -eq $env:COMPUTERNAME) { & $sb $instNames } else { $icArgs = @{ ComputerName = $n ScriptBlock = $sb ArgumentList = (, $instNames) } if ($Credential) { $icArgs['Credential'] = $Credential } Invoke-Command @icArgs } foreach ($row in $rows) { if ($row.Error) { $failed += "${n}\$($row.InstanceName): $($row.Error)" } $row | Select-Object InstanceName, Thumbprint, ForceEncryption, ExpiresOn, IsExpired, @{ Name = 'Node'; Expression = { $n } } } } catch { $failed += "${n}: $($_.Exception.Message)" } } $data = @($data | Where-Object { $_ }) if ($failed) { return New-SqlCertResult -Stage Verify -Status Failed ` -Detail (Add-SqlCertConnectivityHint ($failed -join '; ')) ` -Data $data } $expired = @($data | Where-Object { $_.IsExpired }) if ($expired) { $expDetail = ($expired | ForEach-Object { $exp = if ($_.ExpiresOn) { $_.ExpiresOn.ToString('yyyy-MM-dd') } else { 'unknown expiry' } "$($_.Node)\$($_.InstanceName): expired $exp" }) -join '; ' return New-SqlCertResult -Stage Verify -Status Failed ` -Detail "Bound certificate is expired -- SQL Server will reject it on next restart. $expDetail" ` -Data $data } New-SqlCertResult -Stage Verify -Status Success ` -Detail "Registry binding for: $($SqlInstance -join ', ') on $($Node -join ', ')" ` -Data $data } |