Cool Tips‎ > ‎Connectivity‎ > ‎

Connectivity Get IP Address and Port for Current Instance

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:   Unknown
Date:      20 Dec 2010

Description

Retrieves the IP Address, Port and Protocol details for all endpoints on the current instance.
 

Code

 SELECT      e.name,
            
e.endpoint_id,
            
e.principal_id,
            
e.protocol,
            
e.protocol_desc,
            
ec.local_net_address,
            
ec.local_tcp_port,
            
e.[type],
            
e.type_desc,
            
e.[state],
            
e.state_desc,
            
e.is_admin_endpoint
FROM        sys.endpoints e
LEFT OUTER JOIN sys.dm_exec_connections ec
    
ON ec.endpoint_id = e.endpoint_id
GROUP BY    e.name,
            
e.endpoint_id,
            
e.principal_id,
            
e.protocol,
            
e.protocol_desc,
            
ec.local_net_address,
            
ec.local_tcp_port,
            
e.[type],
            
e.type_desc,
            
e.[state],
            
e.state_desc,
            
e.is_admin_endpoint


Alternatively, a shorter version for just the IP and Port:

SELECT 

   @@SERVERNAME        AS Instance_Name,
  
local_net_address   AS Instance_TCPIP_Address,
  
local_tcp_port      AS Instance_TCPIP_Port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID

ċ
Get TCPIP Port and IP for current instance.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments