Private/Get-SqlActualPort.ps1
|
# ============================================================================= # Script : Get-SqlActualPort.ps1 # Author : Keith Ramsey # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-05-09 Keith Ramsey Phase 2 release polish - DR-202 standard header applied. # 2026-05-14 Keith Ramsey Fix default-instance hive resolution. The default # instance is NOT hive 'MSSQLServer' on modern SQL # (SQL 2025 = MSSQL17.MSSQLSERVER). Always resolve the # instance ID from Instance Names\SQL, which maps every # instance including the default. Surfaced by real-domain # lab testing against SQL Server 2025. # ============================================================================= function Get-SqlActualPort { <# .SYNOPSIS Resolves the actual TCP port for a SQL instance via local or remote registry. .DESCRIPTION Queries the SQL Server registry tree under HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server for the named instance's instance ID, then reads the SuperSocketNetLib\Tcp\IPAll subkey for the configured port or dynamic port. Supports both local (default) and remote (when -ComputerName matches a different host than the local one) registry reads. Remote reads use the .NET RegistryKey APIs and require Remote Registry service running on the target plus appropriate permissions for the calling user. Per Dev Rules �9, when the registry is unreachable or the port can't be resolved, emits a structured warning and defaults to 1433 (the SQL Server default port). Callers can suppress with -WarningAction SilentlyContinue or override with the upstream -ManualPort parameter. .PARAMETER ComputerName Target computer. If unset, '.', 'localhost', or matches $env:COMPUTERNAME, the function reads the local registry directly. .PARAMETER InstanceName SQL instance name (e.g., MSSQLSERVER for default, or a named instance like INST1). .OUTPUTS [int] resolved port (1433 if unresolvable). #> [CmdletBinding()] param( [string]$ComputerName, [string]$InstanceName = 'MSSQLSERVER' ) $isLocal = [string]::IsNullOrWhiteSpace($ComputerName) -or $ComputerName -eq '.' -or $ComputerName -eq 'localhost' -or $ComputerName -eq $env:COMPUTERNAME try { if ($isLocal) { $baseKey = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server' $lookup = Get-ItemProperty "$baseKey\Instance Names\SQL" -ErrorAction Stop $id = $lookup.$InstanceName if (-not $id) { Write-Warning "SQL instance [$InstanceName] not found in local registry; defaulting to 1433." return 1433 } $tcpProps = Get-ItemProperty -Path "$baseKey\$id\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" -ErrorAction Stop $port = if ($tcpProps.TcpPort) { $tcpProps.TcpPort } else { $tcpProps.TcpDynamicPorts } } else { $remoteHive = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $ComputerName) try { $namesKey = $remoteHive.OpenSubKey('SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL') if (-not $namesKey) { Write-Warning "Unable to read SQL instance list from [$ComputerName]; defaulting to 1433." return 1433 } $id = $namesKey.GetValue($InstanceName) $namesKey.Close() if (-not $id) { Write-Warning "SQL instance [$InstanceName] not found on [$ComputerName]; defaulting to 1433." return 1433 } $tcpKey = $remoteHive.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server\$id\MSSQLServer\SuperSocketNetLib\Tcp\IPAll") if (-not $tcpKey) { Write-Warning "TCP IPAll subkey missing for [$InstanceName] on [$ComputerName]; defaulting to 1433." return 1433 } $port = $tcpKey.GetValue('TcpPort') if ([string]::IsNullOrEmpty($port)) { $port = $tcpKey.GetValue('TcpDynamicPorts') } $tcpKey.Close() } finally { $remoteHive.Close() } } if ([string]::IsNullOrEmpty($port)) { Write-Warning "Resolved instance [$InstanceName] but no TCP port set; defaulting to 1433." return 1433 } return [int]$port } catch { $target = if ([string]::IsNullOrWhiteSpace($ComputerName)) { 'local' } else { $ComputerName } Write-Warning "Registry unreachable for instance [$InstanceName] on target [$target]: $($_.Exception.Message). Defaulting to 1433." return 1433 } } |