Private/Get-SqlRoleMetadata.ps1
|
# ============================================================================= # Script : Get-SqlRoleMetadata.ps1 # Author : Keith Ramsey # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-05-09 Keith Ramsey Phase 2 release polish - DR-202 standard header applied. # ============================================================================= function Get-SqlRoleMetadata { <# .SYNOPSIS Returns SPN metadata (service class, default port, requirement flag) for a SQL role. .DESCRIPTION Maps the supported role names to the underlying Kerberos service class and the port SQL uses for that role by default. Roles where SPN registration is not required (e.g., SQL Agent, which shares the engine's SPN) are returned with RequireSpn = $false. Coverage matches BTRD �3 (12-role matrix). Service classes and default ports come from the BTRD; do not silently change them without amending the spec. .PARAMETER Role Role identifier. One of: Engine, Agent, SSAS, SSRS, SSIS, FTS, PolyBase, Browser, ReplayClient, ReplayController, QueryStore, VSSWriter. .NOTES SQL Agent (BTRD SQL-AGT) shares the engine's SPN under most setups; it is listed here for completeness with RequireSpn = $false. Dynamic-port roles (FTS, ReplayClient, ReplayController, QueryStore, VSSWriter) report Port = $null so callers know to source the actual port from runtime discovery rather than from this table. #> [CmdletBinding()] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseSingularNouns', '', Justification = '"Metadata" is a mass noun, not a plural; PSSA classifies any noun ending in "s" as plural.')] param([string]$Role) $Matrix = @{ 'Engine' = @{ ServiceClass = 'MSSQLSvc'; Port = 1433; RequireSpn = $true } 'Agent' = @{ ServiceClass = $null; Port = $null; RequireSpn = $false } 'SSAS' = @{ ServiceClass = 'MSOLAPSvc.3'; Port = 2383; RequireSpn = $true } 'SSRS' = @{ ServiceClass = 'HTTP'; Port = 80; RequireSpn = $true } 'SSIS' = @{ ServiceClass = 'MSSQLSvc'; Port = 135; RequireSpn = $true } 'FTS' = @{ ServiceClass = 'MSSQLSvc'; Port = $null; RequireSpn = $true } 'PolyBase' = @{ ServiceClass = 'MSSQLSvc'; Port = 1443; RequireSpn = $true } 'Browser' = @{ ServiceClass = 'MSOLBDSvc'; Port = 1434; RequireSpn = $true } 'ReplayClient' = @{ ServiceClass = 'MSSQLSvc'; Port = $null; RequireSpn = $true } 'ReplayController' = @{ ServiceClass = 'MSSQLSvc'; Port = $null; RequireSpn = $true } 'QueryStore' = @{ ServiceClass = 'MSSQLSvc'; Port = $null; RequireSpn = $true } 'VSSWriter' = @{ ServiceClass = 'MSSQLSvc'; Port = $null; RequireSpn = $true } } return $Matrix[$Role] } |