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

[cc lang=”vb”]
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
[/cc]