Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Find the TCP port used by a named instance

We know for SQL Server default instance, if TCP/IP protocol is used, the default TCP port number is always 1433. However for a named instance, the TCP port number is dynamic but sometimes, we need to know this port number for various reasons. I find the following way is probably the easiest one:

1. Open a query window from SSMS to connect to the instance of interest.

2. Run:

select local_net_address, local_tcp_port
from sys.dm_exec_connections
where session_id = @@spid

and you get the IP address of the server where the instance resides and the tcp port that is used by the instance.

Comments

Posted by karen on 16 December 2008

Ran this on SQL Server 2005 express and got:

Posted by karen on 16 December 2008

Ran this on SQL Server 2005 Express and got:

local_net_address      local_tcp_port

---------------------------------------

NULL                   NULL

Posted by Gaby Abed on 16 December 2008

Same here, removed the where clause, and got two NULL NULL lines.  Ran on 2005 Developer.

Posted by Jeffrey Yao on 22 December 2008

Thanks for your comments Karen and Gaby.

I believe the server which hosts the sql server instance should be in a domain. Otherwise, there will NULL returned.

I got NULL, NULL from the SQL Server 2K5 Developer edition in my home computer, but I always get the correct results if I run in office connecting to the business servers either test or production environments.

Posted by y.zelenovskiy on 28 January 2009

use NETSTAT from command prompt

Posted by andy on 14 September 2009

This doesn't work unless you have an active connection from a client. Which means that potentially you could get NULL if all connections timeout!!

This caused me a big headache!!

Andy

Leave a Comment

Please register or log in to leave a comment.