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