This question came up as a result of a consolidation exercise. We wanted to ensure the TCP port is the same between the two servers we were looking to consolidate. By default (unless changed) the default TCP Port for SQL Server is 1433. If you have a named instance the TCP port is dynamically configured.
There are a few options when trying to find out this information… one way would be to go right to the registry and look at the TCP settings:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.<InstanceNumber>\MSSQLServer\
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<InstanceName>\MSSQLServer\
Another way would be to run the undocumented system procedure xp_readerrorlog. This will show the current SQL Server log and you can find the row “Server is listening on”. This is NOT a recommended approach because if you cycle your error logs frequenty the current log might not have this information as it is only generated when the SQL Server service restarts. You may have to go through a few logs before finding the information you’re looking for and that’s just a waste of time.
An easier way to find out the same information would be to use the SQL Server Configuration Manager. 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.
If you need to change this port number make sure you restart the SQL Server service as this change will not take affect until you do. (Here is the BOL entry for changing the TCP Port)
(K. Brian Kelley with an additional way to find out this information)
“Another way, if you’re on the system.
View | Select Columns | Check PID | OK
Find sqlservr.exe as the Image Name, note the PID
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).”