Find SQL Server Port

The default port that SQL Server listens on is 1433. This port however is not the same port in all cases as we will come to find. SQL Server has three different ways to communicate. These include:

  • TCP Ports
  • Named Pipes
  • Shared Memory
  • VIA (Virtual Interface Adapater) – Deprecated

 

When communicating, SQL Server can actually use a combination of all three of these protocols for different sessions depending on whether or not they are enabled. You can find more specifics regarding these protocols here.

If that doesn’t make things complicated enough, within the TCP protocol, static or dynamic ports can be defined. If the communication is set to a static port (most common) then it is by default 1433. If it is set to dynamic, then it typically uses a range of ports. It is this range of ports that is not easy to determine, unless you use a command at the command line and correlate the associated process ID with that of the SQL Server instance you are investigating.

The easy way to find the ports is to use the following script which will enumerate all ports that all SQL Server instances are using. (Once you download the script, rename it so SQLListeningPorts.vbs).

The script is also shown below. All you need to do is cut and paste it into notepad, save it with a .vbs extension and double click it. (or right click and ‘open with’ Microsoft Script Host).

Show All SQL Ports VB Script

sProcessName = "sqlservr.exe"

set wshell = createobject("WScript.Shell")
set fso = createobject("Scripting.FileSystemObject")

sOutput = ""

set shExec = wshell.Exec("netstat -ano")

arPIDList = Split(getpids(sProcessName),",")


for each pid in arPIDList
   
    portlist = ""
   
    Do While Not shExec.StdOut.AtEndOfStream
       
        line = shExec.StdOut.ReadLine()
        ' we only care about TCP lines
       if instr(line, "TCP") > 0 then
           
            ' Replace multiple spaces with 1 space
           Do While InStr(1, Line, "  ")
                Line = Replace(Line, "  ", " ")
            Loop
           
            arProcessDtl = Split(Line, " ")
           
            if pid = arProcessDtl(5) then
                portlist = portlist & "," & RIGHT(arProcessDtl(2), Instr(StrReverse(arProcessDtl(2)), ":") -1)
            end if         
           
        end if
   
    Loop
   
    if portlist != '' then
        portlist = Right(portlist,Len(portlist)-1)
        sOutput = sOutput & "PID: " & PID & " listening on port(s): " & portlist & vbcrlf  
    else
        sOutput = sOutput & "PID: " & PID & " has TCP Protocol disabled" & vbcrlf
    end if

next

msgbox sOutput

set fso = Nothing


function getpids(sProcessName)

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\" _
    & strComputer & "rootcimv2")

    Set Processes = objWMIService.ExecQuery _
    ("Select * from Win32_Process where name = '" & sProcessName & "'")

    for each Process in Processes
        Pids = PIDS & "," & Process.ProcessId
    next

    if Pids = "" Then
        msgbox "No Valid SQL Server Instances"
        wscript.quit
    end if

    Pids = trim(Right(Pids,Len(Pids)-1))

    getpids = Pids

end function

2 comments
John 09 Dec 2011 at 9:34 am

Thanks for this article it solved a problem I had been working on for 2 days

Featured Articles

 Site Author

  • Thanks for visiting!