Skip to content

PowerShell: SQL Server Dynamic Port Instance

The following script will get the listening port of a dynamic instance name on Microsoft SQL Server

function Get-SQLInstancePort ([String]$ComputerName="SQL01",[String]$InstanceName="EthHack")
{
    [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement')
    $smoComputer = New-Object 'Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer' $ComputerName
	if ($InstanceName -eq '.')
    { # Default database instance
        $smoTcp = $smoComputer.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
    }
    else 
    {  # Named database instance
        $smoTcp = $smoComputer.ServerInstances[$InstanceName].ServerProtocols['Tcp']
    }
    if ($smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value -eq '') 
    {
        [bool]$smoTcpIsDynamic = $false
        [int]$smoTcpPortNumber = $smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value
    }
    else 
    {
        [bool]$smoTcpIsDynamic = $true
        [int]$smoTcpPortNumber = $smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value
    }
    $smoTcpPortNumber
}
Published inTech

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *