Public/Test-SqlCertClientTrust.ps1
|
# ============================================================================= # Script : Public/Test-SqlCertClientTrust.ps1 # Author : Keith Ramsey # Created : 2026-09-08 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-08 Keith Ramsey Initial (A1, GR-003, DR-034): read-only check of # whether a supplied issuer/root certificate is trusted # in a client's LocalMachine\Root. Never-throw. # ============================================================================= # Decision Contract (Docs/DECISIONS_PHASE7.md DR-034) # ----------------------------------------------------------------------------- # Must : read-only (no ShouldProcess); the caller supplies the issuer/root .cer; # reads the target's LocalMachine\Root (local or remote); one never-throw # SqlCert.Result reporting Trusted true/false with thumbprint + expiry. # ============================================================================= function Test-SqlCertClientTrust { <# .SYNOPSIS Reports whether a client machine trusts a SQL/RS TLS issuer certificate (A1, read-only). .DESCRIPTION A SQL Server (or Reporting Services) TLS connection succeeds only when the client trusts the certificate's issuer. This command reads -- and changes -- nothing: it loads the issuer/root certificate you supply and reports whether that certificate is present in the target machine's LocalMachine\Root (trusted-root) store. Use it to see which clients would reject an encrypted connection before you distribute trust with Add-SqlCertClientTrust. A local or remote target both work; never throws. .PARAMETER CertificatePath Path to the issuer/root certificate file (.cer) to check for. .PARAMETER ComputerName The client machine whose trust store to read. Default local machine. .PARAMETER Credential Credential for a remote read. .OUTPUTS SqlCert.Result Data keys: Thumbprint, Subject, NotAfter, Trusted (bool), ComputerName, CertificatePath. .EXAMPLE Test-SqlCertClientTrust -CertificatePath \\fileshare\ca\CorpRootCA.cer -ComputerName app07 Reports whether app07 already trusts the Corp Root CA -- i.e. whether it would accept an encrypted connection to a SQL server whose certificate that CA issued. .EXAMPLE $clients | Where-Object { -not (Test-SqlCertClientTrust -CertificatePath .\CorpRootCA.cer -ComputerName $_).Data.Trusted } Filters a list of client machines down to the ones that do NOT yet trust the CA -- the ones that need Add-SqlCertClientTrust. .NOTES Steps: 1. Load the supplied issuer/root certificate (thumbprint, subject, expiry). 2. Read the target machine's LocalMachine\Root for that thumbprint (local or remote). 3. Return a Success SqlCert.Result reporting Trusted true/false; any read error -> Failed. Never throws. #> [CmdletBinding()] [OutputType('SqlCert.Result')] param( [Parameter(Mandatory)] [string] $CertificatePath, [string] $ComputerName = $env:COMPUTERNAME, [PSCredential] $Credential ) $credSplat = @{} if ($Credential) { $credSplat.Credential = $Credential } $data = [pscustomobject]@{ Thumbprint = $null Subject = $null NotAfter = $null Trusted = $null ComputerName = $ComputerName CertificatePath = $CertificatePath } try { # 1. Load the issuer/root certificate. $cert = Get-SqlCaCertificate -Path $CertificatePath $data.Thumbprint = $cert.Thumbprint $data.Subject = $cert.Subject $data.NotAfter = $cert.NotAfter # 2. Is it in the target's trusted-root store? $trusted = [bool](Test-SqlCertInRootStore -Thumbprint $cert.Thumbprint -ComputerName $ComputerName @credSplat) $data.Trusted = $trusted # 3. Report. $detail = if ($trusted) { "Issuer '$($cert.Subject)' (thumbprint $($cert.Thumbprint)) IS trusted in LocalMachine\Root on '$ComputerName'." } else { "Issuer '$($cert.Subject)' (thumbprint $($cert.Thumbprint)) is NOT trusted in LocalMachine\Root on '$ComputerName'. Distribute it with Add-SqlCertClientTrust." } New-SqlCertResult -Stage Verify -Status Success -Detail $detail -Data $data } catch { New-SqlCertResult -Stage Verify -Status Failed ` -Detail "Test-SqlCertClientTrust could not read trust on '$ComputerName': $($_.Exception.Message)" -Data $data } } |