Private/Get-SqlEngineCertRow.ps1
|
# ============================================================================= # Script : Private/Get-SqlEngineCertRow.ps1 # Author : Keith Ramsey # Created : 2026-09-07 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-07 Keith Ramsey Initial STUB (D1 skeleton). # 2026-09-07 Keith Ramsey Implemented: read SuperSocketNetLib\Certificate for # the instance (reusing Test-SqlCertBinding's registry # path), resolve in LocalMachine\My, emit a Found row; # Skipped when no binding; never-throw -> Failed row. # Local read inline; remote node via Invoke-Command. # ============================================================================= # D1 surface reader (GR-001, DR-027): SQL Server engine connection binding. # ============================================================================= function Get-SqlEngineCertRow { <# .SYNOPSIS Reads the engine connection-binding certificate surface for the inventory (D1). .DESCRIPTION Resolves the instance's SuperSocketNetLib\Certificate registry value (the same path Test-SqlCertBinding uses), looks the thumbprint up in Cert:\LocalMachine\My for its subject and expiry, and emits one SqlCert.Inventory row: Found when a certificate is bound, Skipped when the instance has no bound cert (SQL falls back to a self-signed one) or the instance is not present, Failed on a read error. A local Node is read inline; a remote Node is read inside an Invoke-Command scriptblock (as Test-SqlCertBinding does). Never throws. .PARAMETER SqlInstance The instance to inspect ('localhost'/'.'/hostname = the default instance; 'HOST\INSTANCE' for a named one). .PARAMETER Node The host computer whose registry to read. Default local machine. .PARAMETER ThresholdDays Expiry threshold passed to the row factory. .PARAMETER Credential Credential for a remote registry read. .OUTPUTS SqlCert.Inventory .NOTES Steps: 1. Resolve the instance name for the registry (localhost/hostname -> MSSQLSERVER; HOST\INST -> INST). 2. Read (local inline, or remote via Invoke-Command) the instance id, then SuperSocketNetLib\Certificate; resolve the cert in LocalMachine\My. 3. Map the read to one SqlCert.Inventory row: Found (bound) / Skipped (no binding or no instance) / Failed (error). Never throws. #> [CmdletBinding()] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseUsingScopeModifierInNewRunspaces', '', Justification = 'Remote Invoke-Command block receives values via param() + -ArgumentList, not closure capture.')] [OutputType('SqlCert.Inventory')] param( [string] $SqlInstance = '', [string] $Node = $env:COMPUTERNAME, [int] $ThresholdDays = 30, [PSCredential] $Credential ) # 1. Registry instance name. $instName = if ($SqlInstance -in @('localhost', '.', '', $Node, $env:COMPUTERNAME)) { 'MSSQLSERVER' } elseif ($SqlInstance -like '*\*') { $SqlInstance.Split('\')[-1] } else { 'MSSQLSERVER' } # 2. Read block (runs local or remote). Returns a hashtable describing the read. $sb = { param($instName) 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) { return @{ Outcome = 'NoInstance'; Detail = "Instance '$instName' not found." } } $key = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instId\MSSQLServer\SuperSocketNetLib" $props = Get-ItemProperty $key -ErrorAction Stop $tp = $props.Certificate if ([string]::IsNullOrWhiteSpace($tp)) { return @{ Outcome = 'NoBinding' } } $cert = Get-ChildItem Cert:\LocalMachine\My -ErrorAction SilentlyContinue | Where-Object { $_ -and $_.Thumbprint -ieq $tp } | Select-Object -First 1 @{ Outcome = 'Found'; Thumbprint = $tp Subject = if ($cert) { $cert.Subject } else { '' } NotAfter = if ($cert) { $cert.NotAfter } else { $null } } } catch { @{ Outcome = 'Error'; Detail = $_.Exception.Message } } } try { $r = if ($Node -eq $env:COMPUTERNAME) { & $sb $instName } else { $ic = @{ ComputerName = $Node; ScriptBlock = $sb; ArgumentList = (, $instName) } if ($Credential) { $ic.Credential = $Credential } Invoke-Command @ic } } catch { return New-SqlCertInventoryRow -Surface Engine -SqlInstance $SqlInstance -Node $Node -ThresholdDays $ThresholdDays ` -Status Failed -Detail "Engine registry read failed on ${Node}: $($_.Exception.Message)" } # 3. Map the read to a row. switch ($r.Outcome) { 'Found' { New-SqlCertInventoryRow -Surface Engine -SqlInstance $SqlInstance -Node $Node -ThresholdDays $ThresholdDays ` -Thumbprint $r.Thumbprint -Subject $r.Subject -NotAfter $r.NotAfter -Status Found ` -Detail "Engine binding for instance '$instName'." } 'NoBinding' { New-SqlCertInventoryRow -Surface Engine -SqlInstance $SqlInstance -Node $Node -ThresholdDays $ThresholdDays ` -Status Skipped -Detail "No engine certificate bound on '$instName' (SQL uses a self-signed certificate)." } 'NoInstance' { New-SqlCertInventoryRow -Surface Engine -SqlInstance $SqlInstance -Node $Node -ThresholdDays $ThresholdDays ` -Status Skipped -Detail $r.Detail } default { New-SqlCertInventoryRow -Surface Engine -SqlInstance $SqlInstance -Node $Node -ThresholdDays $ThresholdDays ` -Status Failed -Detail "Engine registry read failed: $($r.Detail)" } } } |