Public/Export-SqlSpnRegistrationScript.ps1
|
# ============================================================================= # Script : Export-SqlSpnRegistrationScript.ps1 # Author : Keith Ramsey # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-05-21 Keith Ramsey DR-311 v1 surface addition. Renders a New-SqlSpnPlan # output into a clean setspn command bundle for AD- # segregated organisations where the DBA does not have # AD write rights and must hand a script to a sysadmin # for execution. Output carries provenance (module # version, plan GUID, UTC stamp, target account DN) # so the AD admin can prove which version of which # command on which plan produced what they ran. # ============================================================================= function Export-SqlSpnRegistrationScript { <# .SYNOPSIS Renders a SPN plan into a clean setspn command bundle for an AD admin to run. .DESCRIPTION Many organisations separate DBA duties from AD write rights (regulated environments, anywhere with strict role separation). In those shops the dominant workflow is: DBA prepares the SPN registration, hands a script to a sysadmin / AD admin, who executes it from an account with the right ACEs. Without this command, the DBA hand-crafts setspn lines, frequently with mistakes that come back as AD-admin round-trips. DR-311 records the decision to add this command for that workflow. Takes the output of New-SqlSpnPlan via pipeline, emits a clean, executable bundle (Windows .cmd or PowerShell .ps1) containing one setspn -S line per ProposedSpn in the plan. Cross-forest plans include the -T <TargetDomain> flag on every line. The bundle's header carries provenance: the SqlSpnManager module version, the plan's PlanGuid, the UTC generation stamp, and the target account's sAMAccountName + DistinguishedName. The AD admin can prove which command on which plan produced what they ran. Returns the bundle as a string array (one line per element) when -Path is omitted, or writes it to the file at -Path and returns the resolved path. Either way, no SPN registration happens - the actual setspn calls only fire when the bundle is executed by whoever has the AD rights. .PARAMETER Plan Output of New-SqlSpnPlan. Must carry PlanGuid, AccountDn, AccountName, and ProposedSpns. Pipeline-friendly (ValueFromPipeline). .PARAMETER Format Output dialect: Cmd (Windows .cmd batch syntax, matching KCM's familiar Generate output) or PowerShell (.ps1 syntax for shops that execute via Invoke-Command from a PowerShell session). Defaults to Cmd because that is the historic SPN-handoff convention. .PARAMETER Path Optional output file path. If specified, the bundle is written to this path (UTF-8, no BOM) and the resolved path is returned. If omitted, the bundle is emitted to the pipeline as a string array. .EXAMPLE $plan = New-SqlSpnPlan -VerifiedAccount $acct -Infrastructure $infra -Role Engine $plan | Export-SqlSpnRegistrationScript -Path '.\register-svc_sql_prod.cmd' .EXAMPLE New-SqlSpnPlan -VerifiedAccount $acct -Infrastructure $infra -Role Engine | Export-SqlSpnRegistrationScript -Format PowerShell | Set-Clipboard .OUTPUTS When -Path omitted: [string[]] - the bundle, one line per element. When -Path supplied: [string] - the resolved output path. Side effect in both cases: NO SPN registration. The bundle must be executed by whoever holds AD write rights. .NOTES The bundle's setspn invocations use the canonical -S form (DR-301). For cross-forest registrations (Plan.CrossForest = $true), every line carries -T <Plan.TargetDomain> per the engine's existing convention. For Agent and other RequireSpn=$false roles, New-SqlSpnPlan returns $null upstream and this command emits a header-only bundle noting no SPNs were proposed - honest about the no-op, not a silent skip. #> [CmdletBinding()] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '', Justification = 'Export-SqlSpnRegistrationScript is a pure renderer. The optional -Path output follows PowerShell Export-* convention (Export-Csv, Export-Clixml) of not gating user-named file paths behind ShouldProcess. No AD or SPN state changes.')] param( [Parameter(Mandatory=$true, ValueFromPipeline=$true)][PSCustomObject]$Plan, [ValidateSet('Cmd','PowerShell')][string]$Format = 'Cmd', [string]$Path ) begin { $module = Get-Module SqlSpnManager $moduleVersion = if ($module) { $module.Version.ToString() } else { '<unknown>' } $utcStamp = [DateTime]::UtcNow.ToString('yyyy-MM-ddTHH:mm:ssZ') } process { foreach ($required in 'PlanGuid','AccountDn','AccountName','ProposedSpns') { if (-not $Plan.PSObject.Properties[$required]) { throw "Plan is missing required field [$required]; expected New-SqlSpnPlan output." } } $commentChar = if ($Format -eq 'Cmd') { 'REM' } else { '#' } $crossForest = $Plan.PSObject.Properties['CrossForest'] -and $Plan.CrossForest -eq $true $tFlag = if ($crossForest -and $Plan.PSObject.Properties['TargetDomain'] -and $Plan.TargetDomain) { "-T $($Plan.TargetDomain) " } else { '' } $lines = New-Object 'System.Collections.Generic.List[string]' if ($Format -eq 'Cmd') { $lines.Add('@echo off') | Out-Null } $lines.Add("$commentChar ============================================================") $lines.Add("$commentChar SqlSpnManager registration bundle") $lines.Add("$commentChar Module version : $moduleVersion") $lines.Add("$commentChar Plan GUID : $($Plan.PlanGuid)") $lines.Add("$commentChar Generated : $utcStamp") $lines.Add("$commentChar Target account : $($Plan.AccountName)") $lines.Add("$commentChar Account DN : $($Plan.AccountDn)") if ($crossForest) { $lines.Add("$commentChar Cross-forest : -T $($Plan.TargetDomain) applied to every command") } $lines.Add("$commentChar ============================================================") $lines.Add("$commentChar Hand this file to your AD administrator. They must run it") $lines.Add("$commentChar from an elevated session with rights to write to the target") $lines.Add("$commentChar account's servicePrincipalName attribute.") $lines.Add("$commentChar ============================================================") $lines.Add('') $proposed = @($Plan.ProposedSpns) if ($proposed.Count -eq 0) { $lines.Add("$commentChar (no SPNs proposed in this plan; nothing to register.)") } else { foreach ($spn in $proposed) { $lines.Add("setspn $tFlag-S $spn $($Plan.AccountName)") } } if ($Path) { $resolvedDir = Split-Path -Path $Path -Parent if ($resolvedDir -and -not (Test-Path -Path $resolvedDir)) { throw "Output directory does not exist: [$resolvedDir]" } $lines | Set-Content -Path $Path -Encoding UTF8 return (Resolve-Path -Path $Path).ProviderPath } else { return $lines.ToArray() } } } |