Export (0) Print
Expand All

sys.tcp_endpoints (Transact-SQL)

Contains one row for each TCP endpoint that is in the system. The endpoints that are described by sys.tcp_endpoints provide an object to grant and revoke the connection privilege. The information that is displayed regarding ports and IP addresses is not used to configure the protocols and may not match the actual protocol configuration. To view and configure protocols, use SQL Server Configuration Manager.

Applies to: SQL Server (SQL Server 2008 through current version).

Column name

Data type

Description

<inherited columns>

Inherits columns from sys.endpoints.

port

int

The port number that the endpoint is listening on. Is not nullable.

is_dynamic_port

bit

1 = Port number was dynamically assigned.

Is not nullable.

ip_address

nvarchar(45)

Listener IP address as specified by the LISTENER_IP clause. Is nullable.

Execute the following query to gather information about the endpoints and connections. Endpoints without current connections or without TCP connections will appear with NULL values. Add the WHERE clause WHERE des.session_id = @@SPID to return information about the current connection.

SELECT des.login_name, des.host_name, program_name,  dec.net_transport, des.login_time, 
e.name AS endpoint_name, e.protocol_desc, e.state_desc, e.is_admin_endpoint, 
t.port, t.is_dynamic_port, dec.local_net_address, dec.local_tcp_port 
FROM sys.endpoints AS e
LEFT JOIN sys.tcp_endpoints AS t
   ON e.endpoint_id = t.endpoint_id
LEFT JOIN sys.dm_exec_sessions AS des
   ON e.endpoint_id = des.endpoint_id
LEFT JOIN sys.dm_exec_connections AS dec
   ON des.session_id = dec.session_id;

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

Community Additions

ADD
Show:
© 2014 Microsoft