Private/Get-RsCertRow.ps1
|
# ============================================================================= # Script : Private/Get-RsCertRow.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: resolve the RS/PBIRS config object via # Get-SqlRsInstance, enumerate SSL bindings via # Invoke-SqlRsCimBinding (reusing Test-RsCertBinding's # WMI path), dedupe by thumbprint (row-per-cert), and # resolve subject/expiry from LocalMachine\My (local # inline / remote Invoke-Command, the engine reader's # idiom). Skipped when RS absent or HTTP-only; # never-throw -> Failed row. # ============================================================================= # D1 surface reader (GR-001, DR-027): Reporting Services / PBIRS HTTPS binding. # ============================================================================= function Get-RsCertRow { <# .SYNOPSIS Reads the Reporting Services / PBIRS certificate surface for the inventory (D1). .DESCRIPTION Resolves the RS/PBIRS MSReportServer_ConfigurationSetting object (via Get-SqlRsInstance, the same helper Test-RsCertBinding uses), enumerates the HTTPS SSL certificate bindings (Invoke-SqlRsCimBinding -> ListSSLCertificateBindings), de-duplicates by thumbprint so each bound certificate is one row (DR-026), and looks each thumbprint up in Cert:\LocalMachine\My for its subject and expiry. Emits one SqlCert.Inventory row per bound certificate (Found), a Skipped row when RS is absent on the node or bound HTTP-only, and a Failed row on a read error. A local Node is read inline; a remote Node's store is read inside an Invoke-Command block (as the engine reader does). Never throws. .PARAMETER SqlInstance The instance whose RS/PBIRS surface to read ('localhost'/'.'/hostname = the default RS instance MSSQLSERVER; 'HOST\INSTANCE' maps to instance INSTANCE). .PARAMETER Node The host computer whose RS WMI provider and cert store to read. Default local machine. .PARAMETER ThresholdDays Expiry threshold passed through to the row factory. .PARAMETER Credential Credential for a remote WMI / store read. .OUTPUTS SqlCert.Inventory .NOTES Steps: 1. Resolve the RS instance name for the WMI provider (localhost/hostname -> MSSQLSERVER; HOST\INST -> INST). 2. Resolve the RS config object (Get-SqlRsInstance -> $null when RS is not installed -> Skipped). 3. Enumerate SSL bindings (Invoke-SqlRsCimBinding); map each distinct thumbprint to the application(s) it serves; no binding -> Skipped (HTTP only). 4. For each distinct thumbprint resolve subject/expiry from LocalMachine\My (local inline, or remote via Invoke-Command) and emit one Found row. 5. Any read error -> one Failed row. Never throws. #> [CmdletBinding()] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseUsingScopeModifierInNewRunspaces', '', Justification = 'Remote Invoke-Command block receives the thumbprint via param() + -ArgumentList, not closure capture.')] [OutputType('SqlCert.Inventory')] param( [string] $SqlInstance = '', [string] $Node = $env:COMPUTERNAME, [int] $ThresholdDays = 30, [PSCredential] $Credential ) # 1. RS instance name (same derivation as the engine reader). $rsInstName = if ($SqlInstance -in @('localhost', '.', '', $Node, $env:COMPUTERNAME)) { 'MSSQLSERVER' } elseif ($SqlInstance -like '*\*') { $SqlInstance.Split('\')[-1] } else { 'MSSQLSERVER' } $rowArgs = @{ Surface = 'ReportingServices'; SqlInstance = $SqlInstance; Node = $Node; ThresholdDays = $ThresholdDays } # Cert-store lookup (thumbprint -> subject/expiry). Local inline / remote via Invoke-Command. $certSb = { param($tp) $c = Get-ChildItem Cert:\LocalMachine\My -ErrorAction SilentlyContinue | Where-Object { $_ -and $_.Thumbprint -ieq $tp } | Select-Object -First 1 if ($c) { @{ Subject = $c.Subject; NotAfter = $c.NotAfter } } else { @{ Subject = ''; NotAfter = $null } } } try { # 2. Resolve the RS config object (never-throw helper -> $null when absent). $cfgArgs = @{ InstanceName = $rsInstName; ComputerName = $Node } if ($Credential) { $cfgArgs.Credential = $Credential } $cfg = Get-SqlRsInstance @cfgArgs if (-not $cfg) { return New-SqlCertInventoryRow @rowArgs -Status Skipped ` -Detail "Reporting Services instance '$rsInstName' not found on '$Node'." } # 3. Enumerate SSL bindings; map distinct thumbprint -> application(s). $bind = Invoke-SqlRsCimBinding -ConfigObject $cfg if ($null -eq $bind -or $bind.HRESULT -ne 0) { $hr = if ($bind) { $bind.HRESULT } else { 'null' } return New-SqlCertInventoryRow @rowArgs -Status Failed ` -Detail "ListSSLCertificateBindings failed for RS '$rsInstName' on '$Node' (HRESULT=$hr)." } $apps = @($bind.Application) $hashes = @($bind.CertificateHash) $map = [ordered]@{} for ($i = 0; $i -lt $apps.Count; $i++) { $h = $hashes[$i] if (-not [string]::IsNullOrWhiteSpace($h)) { if (-not $map.Contains($h)) { $map[$h] = [System.Collections.Generic.List[string]]::new() } $map[$h].Add([string]$apps[$i]) } } if ($map.Count -eq 0) { return New-SqlCertInventoryRow @rowArgs -Status Skipped ` -Detail "RS '$rsInstName' present on '$Node'; no certificate bound (HTTP only)." } # 4. One Found row per distinct bound certificate. foreach ($tp in $map.Keys) { $look = if ($Node -eq $env:COMPUTERNAME) { & $certSb $tp } else { $ic = @{ ComputerName = $Node; ScriptBlock = $certSb; ArgumentList = (, $tp) } if ($Credential) { $ic.Credential = $Credential } try { Invoke-Command @ic } catch { @{ Subject = ''; NotAfter = $null } } } $appList = (@($map[$tp]) | Sort-Object -Unique) -join ', ' New-SqlCertInventoryRow @rowArgs -Thumbprint $tp -Subject $look.Subject -NotAfter $look.NotAfter -Status Found ` -Detail "RS '$rsInstName' HTTPS binding ($appList)." } } catch { # 5. Never-throw. New-SqlCertInventoryRow @rowArgs -Status Failed ` -Detail "Reporting Services read failed on '$Node': $($_.Exception.Message)" } } |