Public/Test-SqlPolyBaseCertificate.ps1
|
# ============================================================================= # Script : Public/Test-SqlPolyBaseCertificate.ps1 # Author : Keith Ramsey # Created : 2026-09-08 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-08 Keith Ramsey Initial (A3, GR-011, DR-044): read-only PolyBase # readiness -- is PolyBase installed, the scale-out # compute nodes, and (optionally) the certificate bound # to the PolyBase engine HTTP.sys port (reusing A2's # seam). Verifiable facts only; never-throw. # ============================================================================= # Decision Contract (Docs/DECISIONS_PHASE7.md DR-044) # ----------------------------------------------------------------------------- # Must : read-only; documented/verifiable sources only (SERVERPROPERTY # IsPolyBaseInstalled, sys.dm_exec_compute_nodes, and A2's HTTP.sys seam # for an -IPPort); NO fabricated queries against PolyBase internals; one # never-throw SqlCert.Result. Managing PolyBase internal certs is out. # ============================================================================= function Test-SqlPolyBaseCertificate { <# .SYNOPSIS Reports PolyBase scale-out certificate readiness, read-only (A3). .DESCRIPTION Reports what is verifiable about PolyBase's TLS surface: whether PolyBase is installed (SERVERPROPERTY 'IsPolyBaseInstalled'), the compute nodes in the scale-out group (sys.dm_exec_compute_nodes), and -- when you supply the PolyBase engine -IPPort -- the certificate bound to that HTTP.sys port (reusing the SSAS/A2 HTTP.sys seam), with its subject and expiry. Read-only; never throws. It does not bind, rotate, or manage PolyBase's internal scale-out-group certificates -- those are PolyBase-managed and not documented as externally scriptable, so A3 reports rather than pretends to control them (DR-044). .PARAMETER SqlInstance The instance to query. Default 'localhost'. .PARAMETER IPPort Optional: the PolyBase engine HTTP.sys IP:port to check for a certificate binding. .PARAMETER Credential SQL or Windows credential for the connection. .OUTPUTS SqlCert.Result Data keys: IsPolyBaseInstalled, ComputeNodes @(NodeId/Name/Address), EnginePortChecked, EnginePort, EnginePortThumbprint, EnginePortSubject, EnginePortNotAfter. .EXAMPLE Test-SqlPolyBaseCertificate -SqlInstance sql01 Reports whether PolyBase is installed on sql01 and the compute nodes in its scale-out group. .EXAMPLE Test-SqlPolyBaseCertificate -SqlInstance sql01 -IPPort '0.0.0.0:16450' Also checks the certificate bound to the PolyBase engine HTTP.sys port. .NOTES Steps: 1. Read SERVERPROPERTY('IsPolyBaseInstalled') via the read seam. 2. If installed, read the scale-out compute nodes (sys.dm_exec_compute_nodes). 3. If -IPPort was supplied, read the certificate bound to that HTTP.sys port (A2's seam) and resolve its subject/expiry. 4. Return a Success SqlCert.Result with the findings; any read error -> Failed. Never throws. #> [CmdletBinding()] [OutputType('SqlCert.Result')] param( [string] $SqlInstance = 'localhost', [string] $IPPort, [PSCredential] $Credential ) $credSplat = @{} if ($Credential) { $credSplat.Credential = $Credential } $portChecked = $PSBoundParameters.ContainsKey('IPPort') $data = [pscustomobject]@{ IsPolyBaseInstalled = $false ComputeNodes = @() EnginePortChecked = $portChecked EnginePort = $null EnginePortThumbprint = $null EnginePortSubject = $null EnginePortNotAfter = $null } if ($portChecked) { $data.EnginePort = $IPPort } try { # 1. Is PolyBase installed? $inst = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database 'master' @credSplat ` -Query "SELECT CAST(ISNULL(SERVERPROPERTY('IsPolyBaseInstalled'), 0) AS int) AS Installed;") $installed = ([int]$inst[0].Installed -eq 1) $data.IsPolyBaseInstalled = $installed # 2. Scale-out compute nodes (only when installed). if ($installed) { $nodes = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database 'master' @credSplat ` -Query "SELECT compute_node_id AS NodeId, name AS Name, address AS Address FROM sys.dm_exec_compute_nodes;") $data.ComputeNodes = @(foreach ($n in $nodes) { [pscustomobject]@{ NodeId = [int]$n.NodeId; Name = [string]$n.Name; Address = [string]$n.Address } }) } # 3. Optional engine-port HTTP.sys certificate (reuse A2's seam). if ($portChecked) { $tp = Invoke-SqlCertHttpBinding -Action Get -IPPort $IPPort -ComputerName $env:COMPUTERNAME @credSplat if (-not [string]::IsNullOrWhiteSpace($tp)) { $data.EnginePortThumbprint = $tp $cert = Get-ChildItem Cert:\LocalMachine\My -ErrorAction SilentlyContinue | Where-Object { $_ -and $_.Thumbprint -ieq $tp } | Select-Object -First 1 if ($cert) { $data.EnginePortSubject = $cert.Subject; $data.EnginePortNotAfter = $cert.NotAfter } } } # 4. Report. $detail = if (-not $installed) { "PolyBase is not installed on '$SqlInstance'." } else { $portNote = if ($portChecked) { if ($data.EnginePortThumbprint) { " Engine port '$IPPort' has certificate $($data.EnginePortThumbprint) bound." } else { " Engine port '$IPPort' has no certificate bound." } } else { '' } "PolyBase is installed on '$SqlInstance' with $(@($data.ComputeNodes).Count) compute node(s).$portNote" } New-SqlCertResult -Stage Verify -Status Success -Detail $detail -Data $data } catch { New-SqlCertResult -Stage Verify -Status Failed ` -Detail "Test-SqlPolyBaseCertificate could not read PolyBase state on '$SqlInstance': $($_.Exception.Message)" -Data $data } } |