Identifying system endpoints in SQL Server 2005

  • Article

SQL Server 2005 introduces the concept of an ‘Endpoint’. Endpoints are objects that represent a communication point between the server and a client. SQL Server automatically creates an endpoint for each of the four protocols (TCP/IP, Shared Memory, Named Pipe, VIA) that accept TDS connections. An additional endpoint is created for the dedicated administrator connection (DAC), which can only be used by members of the sysadmin fixed server role. These five endpoints are referred to as system endpoints in the remainder of the articleUnlike user defined endpoints, there are restrictions on the modification of these system endpoints. System endpoints cannot be DROPPED. Only the owner and the state can be changed on the system endpoints (via ALTER ENDPOINT). One cannot disable the system endpoints, but can stop and start them.

The ‘endpoint_id’ column in the sys.endpoints catalog view is the suggested method of identifying system endpoints. An endpoint with an ID less than 65536 is a system endpoint.

The following query shows all the system endpoints.

select name, endpoint_id from sys.endpoints where endpoint_id < 65536

Results of the query

name                                                             endpoint_id

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

Dedicated Admin Connection                         1

TSQL Local Machine                                       2

TSQL Named Pipes                                         3

TSQL Default TCP                                          4

TSQL Default VIA                                           5

Now if an attempt is made to drop one of these endpoints, the server will respond with the following error.

DROP endpoint [TSQL Default TCP]

returns the following error string.

Msg 7873, Level 16, State 1, Server FOOBAR, Line 1

The endpoint "TSQL Default TCP" is a built-in endpoint and cannot be dropped.

Use the protocol configuration utilities to ADD or DROP Transact-SQL endpoints.

Anuradha Rawal, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights