Tuesday, October 11, 2011

Finding the Port Number for a particular SQL Server Instance

One of the developers recently asked me this question: “I have a SQL Server instance running – how can I tell what port it is running on?”.  There are a couple of ways of finding this information.

1) Using the GUI:

If you expand the SQL Server Network Configuration and select Protocols for MSSQLSERVER (default instance) in the right hand pane you’ll see your protocols (Shared Memory, Named Pipes, TCP/IP, VIA).  If you double click on TCP/IP the properties dialog box will appear and by selecting the IP Addresses tab you will now see your IP addresses and which TCP port they are running under.

2)  Check the error log.  You will see an entry like: “Server is listening on [ 'any' <ipv4> 1433].”  The last 4 numbers denote the TCP/IP port number that is being used by that particular instance of SQL Server.

3)  Registry entry: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

and you will see TCPPort as one of the entries and it’s value represents the port number for that instance.  In case you are using a named instance, then the registry entry will be: HKLM\Software\Microsoft\Microsoft SQL Server\<name of the instance>\MSSQLServer\SuperSocketNetLib\TCP

4) You can also chose to trace the client to server communication by using Microsoft Network Monitor or a network sniffer utility.  You can also use the TCP/IP netstat utility

Task Manager
View | Select Columns | Check PID | OK
Find sqlservr.exe as the Image Name, note the PID
Command prompt:
netstat -ano | findstr *PID*

The PID is displayed in the far right. Look for the TCP entry. The port will be after the colon (e.g. 0.0.0.0:1433 means the port is 1433).”

0 comments:

Post a Comment