Private/Invoke-SqlCertInventoryQuery.ps1
|
# ============================================================================= # Script : Private/Invoke-SqlCertInventoryQuery.ps1 # Author : Keith Ramsey # Created : 2026-09-07 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-07 Keith Ramsey Initial: shared read-only T-SQL execution seam for # the DMV-only inventory surfaces (TDE, backup, # endpoint, cell -- DR-027). dbatools preferred # (DR-003 optional dependency, probed at run), then # the SqlServer module; neither present -> throw a # clear message so the caller's never-throw wrapper # reports a Failed row (DR-027). The one seam the # T-SQL readers mock. # ============================================================================= # Decision Contract (Docs/DECISIONS_PHASE7.md DR-027; module DR-003) # ----------------------------------------------------------------------------- # Must : read-only SELECT only; no writes. Optional-dependency probe, never an # install. Throws (not swallows) on a provider error so the reader turns # it into a Failed row. Live provider call is held for the GR-001 lab # proof; unit tests mock this function or the provider cmdlet. # ============================================================================= function Invoke-SqlCertInventoryQuery { <# .SYNOPSIS Runs one read-only inventory SELECT against a SQL instance via the available provider. .DESCRIPTION The shared T-SQL seam for the DMV-only cert surfaces (TDE, backup-encryption, endpoint, cell). Probes for a query provider at run -- dbatools first (the module's optional dependency, DR-003), then the SqlServer module -- and executes the SELECT read-only. When neither provider is present it throws a clear, actionable message; the calling reader catches that and emits a Failed row naming the missing provider (DR-027), rather than the inventory failing outright. Returns the result rows (possibly empty). .PARAMETER SqlInstance The instance to query ('HOST', 'HOST\INSTANCE', or 'localhost'). .PARAMETER Query The read-only SELECT to run. Callers pass fully-qualified catalog/DMV queries. .PARAMETER Database Initial database context. Default 'master' (the inventory queries fully qualify msdb/master, so this is only the login's landing database). .PARAMETER Credential SQL or Windows credential for the connection; omitted for the caller's own token. .OUTPUTS The provider's result rows (DataRow/PSObject), or none. .NOTES Steps: 1. Probe for a query provider -- dbatools preferred and imported EXPLICITLY (DR-003; auto-import is unreliable in a module scope, so fall back to SqlServer if dbatools will not import), then the SqlServer module. 2. Execute the SELECT read-only via the available provider (dbatools -EnableException / Invoke-Sqlcmd -ErrorAction Stop) so a failure throws rather than being swallowed. 3. When no provider is present, throw a clear message so the caller reports a Failed row (DR-027). Return the rows. #> [CmdletBinding()] param( [Parameter(Mandatory)] [string] $SqlInstance, [Parameter(Mandatory)] [string] $Query, [string] $Database = 'master', [PSCredential] $Credential ) # 1. Probe providers (optional-dependency, never an install -- DR-003). # dbatools must be imported EXPLICITLY: calling Invoke-DbaQuery cold relies on # auto-import, which fails inside a module scope on some hosts even though an # explicit Import-Module succeeds. If dbatools is present but will not import, # fall through to the SqlServer module rather than dying (DR-003). $useDbatools = $false if (Get-Module -ListAvailable -Name dbatools) { try { Import-Module dbatools -ErrorAction Stop; $useDbatools = $true } catch { $useDbatools = $false } } if ($useDbatools) { $p = @{ SqlInstance = $SqlInstance; Query = $Query; Database = $Database; EnableException = $true } if ($Credential) { $p.SqlCredential = $Credential } # 2. dbatools path (read-only SELECT; -EnableException makes failures throw). dbatools trusts # the server TLS cert via its own connection defaults; Invoke-DbaQuery has no such parameter. return Invoke-DbaQuery @p } elseif (Get-Module -ListAvailable -Name SqlServer) { # TrustServerCertificate: a cert-management tool must reach a server whose own TLS cert is # often self-signed / not-yet-trusted (the case this tool exists to fix), so trust the server # cert here (matches SSMS / sqlcmd -C). SQL 2025 defaults to mandatory connection encryption, # so an untrusted self-signed cert would otherwise refuse the login. $p = @{ ServerInstance = $SqlInstance; Query = $Query; Database = $Database; ErrorAction = 'Stop'; TrustServerCertificate = $true } if ($Credential) { $p.Credential = $Credential } # 2. SqlServer-module path. return Invoke-Sqlcmd @p } # 3. No provider -- throw so the reader emits a Failed row naming the fix (DR-027). throw "No SQL query provider available to read this surface on '$SqlInstance'. Install the dbatools or SqlServer module to inventory the TDE/backup/endpoint/cell surfaces." } |