TCP listening ports and ENDPOINT ports behavior

  • Article

Endpoint configuration seems to be creating some confusion. I hope that this will clarify some of the behavior as it applies to TCP endpoints.

With a default configuration of SQL Server, try the following:

> select name, port, is_dynamic_port, state from sys.tcp_endpoints
> go
name port is_dynamic_port state
--------------------------- ---- --------------- -----
Dedicated Admin Connection 0 1 0
TSQL Default TCP 0 1 0

At first, it may seem that the “port” value in this table is incorrect. The server is listening on port 1433, not port 0. In addition, if your instance is configured to listen on different or multiple ports, they do not appear here. Confused? I was too. (Also, note that state 0 means "started" and state 1 means "stopped").

The behavior you are seeing is, in fact, by design. A zero for a port value is effectively a wildcard, matching any endpoint of that type that doesn't have a more specific rule. The endpoint configuration does not specify what ports clients can use to connect to the server. Rather, it specifies the rules to apply to a connection when it is made.

So, the default TCP port or any other TCP port with no specific endpoint defined will use this default rule. Here is an example of how this works:

First, issue "alter endpoint [TSQL Default TCP] state = stopped" and see that we can no longer connect via TCP to the server:

> select name, port, is_dynamic_port, state from sys.tcp_endpoints
> go
name port is_dynamic_port state
--------------------------- ---- --------------- -----
Dedicated Admin Connection 0 1 0
TSQL Default TCP 0 1 1

For now, re-enable that port using "alter endpoint [TSQL Default TCP] state = started" and we can connect again using TCP on the default port.

If you now use SQLServer Configuration Manager to add a new port for SQLServer to listen on, say, port 6060 and restart, you can now connect using TCP on port 1433 or port 6060.

Then issue "alter endpoint [TSQL Default TCP] state = stopped" and we can no longer connect on either port.

Then issue "create endpoint [MyEndpoint] state = stopped as tcp (listener_port = 6060) FOR tsql ()" and we still can't connect on either port, because they are both still "stopped":

> select name, port, is_dynamic_port, state from sys.tcp_endpoints
> go
name port is_dynamic_port state
--------------------------- ---- --------------- -----
Dedicated Admin Connection 0 1 0
TSQL Default TCP 0 1 1
MyEndpoint 6060 0 1

Now issue "alter endpoint [TSQL Default TCP] state = started" and we can connect on 1433 but not on 6060. This is because there is a specific rule for port 6060 which is applied. On port 1433, there is no specific rule, so the default rules apply and the connection is allowed.

If we now issue "alter endpoint [MyEndpoint] state = started" and we can connect via TCP on both port 1433 and port 6060.

Finally, if we issue "alter endpoint [TSQL Default TCP] state = stopped" and we can connect via TCP on port 6060 but not on port 1433.

David Benoit
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights