Reputation: 4318

How to Identify port number of SQL server

I Install SQL server in my system and I have to check on which port number SQL is working in my system

Upvotes: 69

Answers (8)

Marcello Miorelli

Reputation: 3678

I loved the code from Bartosz x above, and from that I started to build up on that and ended up with a script that I have used in many patching and other troubleshooting situations.

It because a bit large, but here it is:

#-----------------------------------------------------------------------------------------------------
# Get-SQLInstancePorts 
# Function to list SQL Server instances and their ports from the registry
# Marcello Miorelli
# 24-may-2024
#-----------------------------------------------------------------------------------------------------

function Get-SQLInstancePorts {
    $instances = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
    $ports = @()

    foreach ($instance in $instances) {
        if ($instance -eq "MSSQLSERVER") {
            $keyPath = "HKLM:\SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP"
        } else {
            $keyPath = "HKLM:\SOFTWARE\MICROSOFT\Microsoft SQL Server\$instance\MSSQLServer\Supersocketnetlib\TCP"
        }

        try {
            $tcpPort = Get-ItemProperty -Path $keyPath -Name TcpPort -ErrorAction SilentlyContinue | Select-Object -ExpandProperty TcpPort -ErrorAction SilentlyContinue
            $tcpDynamicPorts = Get-ItemProperty -Path $keyPath -Name TcpDynamicPorts -ErrorAction SilentlyContinue | Select-Object -ExpandProperty TcpDynamicPorts -ErrorAction SilentlyContinue
            $instanceDisplayName = Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\SQLWriter" -Name DisplayName -ErrorAction SilentlyContinue | Select-Object -ExpandProperty DisplayName -ErrorAction SilentlyContinue
            
            if ($tcpPort) {
                $ports += [pscustomobject]@{
                    Instance_Name = $instance
                    DisplayName   = $instanceDisplayName
                    PortType      = "Static"
                    Port          = $tcpPort
                }
            }

            if ($tcpDynamicPorts) {
                $ports += [pscustomobject]@{
                    Instance_Name = $instance
                    DisplayName   = $instanceDisplayName
                    PortType      = "Dynamic"
                    Port          = $tcpDynamicPorts
                }
            }
        } catch {
            Write-Host "Failed to retrieve port information for instance: $instance"
        }
    }

    return $ports
}

#----------------------------------------------------------
# function Get-FullyQualifiedComputerName 
# How do I get the localhost name in PowerShell?
#
# https://stackoverflow.com/a/78528603/1501497
# https://stackoverflow.com/a/72551767/1501497
#
# Marcello Miorelli
# 24-may-2024
#----------------------------------------------------------




function Get-FullyQualifiedComputerName {
    Add-Type -TypeDefinition @'
    public enum COMPUTER_NAME_FORMAT{
        ComputerNameNetBIOS,
        ComputerNameDnsHostname,
        ComputerNameDnsDomain,
        ComputerNameDnsFullyQualified,
        ComputerNamePhysicalNetBIOS,
        ComputerNamePhysicalDnsHostname,
        ComputerNamePhysicalDnsDomain,
        ComputerNamePhysicalDnsFullyQualified,
        ComputerNameMax,
    }
    public static class Kernel32{
        [System.Runtime.InteropServices.DllImport("Kernel32.dll", SetLastError = true, CharSet = System.Runtime.InteropServices.CharSet.Auto)]
        public static extern bool GetComputerNameEx(COMPUTER_NAME_FORMAT NameType, System.Text.StringBuilder lpBuffer, ref uint lpnSize);
    }
'@ | Out-Null

    $len = 0
    [Kernel32]::GetComputerNameEx([COMPUTER_NAME_FORMAT]::ComputerNameDnsFullyQualified, $null, [ref]$len) | Out-Null
    $sb = [System.Text.StringBuilder]::new([int]$len)
    $len = $sb.Capacity
    [Kernel32]::GetComputerNameEx([COMPUTER_NAME_FORMAT]::ComputerNameDnsFullyQualified, $sb, [ref]$len) | Out-Null
    return $sb.ToString().Trim()
}



# Function to list open SQL Server ports
function List-SQLServerPorts {
    $sqlProcesses  = Get-Process | Where-Object { $_.ProcessName -like "*SQL*" } | Select-Object -Property ProcessName, Id
    $instancePorts = Get-SQLInstancePorts

    $ports = @()

    $ServerName = Get-FullyQualifiedComputerName 
    #[Environment]::MachineName

    foreach ($sqlProc in $sqlProcesses) {
        $connections = Get-NetTCPConnection | Where-Object { $_.OwningProcess -eq $sqlProc.Id }

        foreach ($conn in $connections) {
            $instancePort = $instancePorts | Where-Object { $_.Port -eq $conn.LocalPort }
            $instanceName = if ($instancePort) { $instancePort.Instance_Name } else { "Unknown" }
            $portType = if ($instancePort) { $instancePort.PortType } else { "Unknown" }
            $displayName = if ($instancePort) { $instancePort.DisplayName } else { "Unknown" }

            $ports += [pscustomobject]@{
                Server_Name    = $ServerName  
                Instance_Name  = $instanceName
                DisplayName    = $displayName
                Process_Name   = $sqlProc.ProcessName
                Local_Address  = "$($conn.LocalAddress):$($conn.LocalPort)"
                Remote_Address = "$($conn.RemoteAddress):$($conn.RemotePort)"
                State          = $conn.State
                Port_Type      = $portType
                Port           = $conn.LocalPort
            }
        }
    }

    if ($ports.Count -eq 0) {
        Write-Host "No SQL Server ports found."
    } else {
        $ports | Format-Table -AutoSize
    }
}

# Example usage
List-SQLServerPorts

here is the outcome in one of my servers:

Upvotes: 0

Ankit jain

Reputation: 4318

Open Run in your system.
Type %windir%\System32\cliconfg.exe
Click on ok button then check that the "TCP/IP Network Protocol Default Value Setup" pop-up is open.
Highlight TCP/IP under the Enabled protocols window.
Click the Properties button.
Enter the new port number, then click OK.

enter image description here

Upvotes: 46

Bartosz X

Reputation: 2798

PowerShell solution that shows all of the instances on the host as well as their incoming traffic addresses. The second bit might be helpful if all you know is the DNS:

ForEach ($SQL_Proc in Get-Process | Select-Object -Property ProcessName, Id | Where-Object {$_.ProcessName -like "*SQL*"})
{
    Get-NetTCPConnection | `
     Where-Object {$_.OwningProcess -eq $SQL_Proc.id} | `
      Select-Object -Property `
                                @{Label ="Process_Name";e={$SQL_Proc.ProcessName}}, `
                                @{Label ="Local_Address";e={$_.LocalAddress + ":" + $_.LocalPort }},  `
                                @{Label ="Remote_Address";e={$_.RemoteAddress + ":" + $_.RemotePort}}, State | `
      Format-Table
}

Upvotes: 7

Unsal Aslan

Reputation: 131

This query works for me:

SELECT DISTINCT 
    local_tcp_port 
FROM sys.dm_exec_connections 
WHERE local_tcp_port IS NOT NULL

Upvotes: 11

Sunil Agarwal

Reputation: 4277

You can also use this query

USE MASTER GO xp_readerrorlog 0, 1, N'Server is listening on' GO

Source : sqlauthority blog

Upvotes: 49

Punter015

Reputation: 1786

Open SQL Server Management Studio
Connect to the database engine for which you need the port number
Run the below query against the database

select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address is not null

The above query shows the local IP as well as the listening Port number

Upvotes: 101