TCP listening ports and ENDPOINT ports behavior

TCP listening ports and ENDPOINT ports behavior

  • Comments 2

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

Leave a Comment
  • Please add 2 and 8 and type the answer here:
  • Post
  • In SQL Server 2008, do you have to create a TCP Endpoint if you want SQL Server to listen to a specific IP Address and a specific port? In SQL Server Configuration Manager, the TCP/IP protocol is enabled and all the Dynamic TCP Ports and TCP Port fields for each IPx has been cleared except for one IP address. (As directed in http://msdn.microsoft.com/en-us/library/ms177440.aspx) On that one IPx the TCP Port has been set to 2505 and on the IPAll the TCP Port has been set to 2505. If the “Listen All” property is set to “No”, then remote client connections cannot connect to the server. If the “Listen All” property is set to “Yes”, then remote client connections can connect to the server. Is SQL Server really listening on all ports or does having the TCP Port set on the one IPx and the IPALL stop SQL Server from listening on all ports? The concern is an external firewall (not the Windows Firewall) requires a specific IP Address and Port number. This article (http://msdn.microsoft.com/en-us/library/ms191220.aspx) seems to indicate that a TCP endpoint is needed to override the default TCP endpoint. This installation is SQL Server 2008 (64-bit) Developer Edition with CU3 on Windows Server 2008 Standard SP1.

    In SQL Server 2005, the “Listen All” property was set to “No” and only one IPx had the TCP Port set to a specific number. And this seemed to work for remote client connections.

  • I am just wondering why a default instance install is not listening on Port 1434 for the DAC?  The firewall is not blocking Port 1434 either?

Page 1 of 1 (2 items)