Edit

Share via

Configure the Database Engine to listen on multiple TCP ports

Applies to: SQL Server

This article describes how to configure the Database Engine to listen on multiple TCP ports in SQL Server by using SQL Server Configuration Manager. When TCP/IP is enabled for SQL Server, the Database Engine listens for incoming connections on a connection point consisting of an IP address and TCP port number. The following procedures create a tabular data stream (TDS) endpoint, so that SQL Server listens on another TCP port.

Possible reasons to create a second TDS endpoint include:

  • Increase security by configuring the firewall to restrict access to the default endpoint to local client computers on a specific subnet. Maintain Internet access to SQL Server for your support team by creating a new endpoint that the firewall exposes to the Internet, and restricting connection rights to this endpoint to your server support team.

  • Affinitizing connections to specific processors when using Non-Uniform Memory Access (NUMA).

Configuring a TDS endpoint consists of the following steps, which can be done in any order:

  • Create the TDS endpoint for the TCP port, and restore access to the default endpoint if appropriate.

  • Grant access to the endpoint to the desired server principals.

  • Specify the TCP port number for the selected IP address.

For more information about the default Windows Firewall settings, and a description of the TCP ports that affect the Database Engine, Analysis Services, Reporting Services, and Integration Services, see Configure the Windows Firewall to allow SQL Server access.

Create a TDS endpoint

Issue the following statement to create an endpoint named CustomConnection for port 1500 for all available TCP addresses on the server.

USE master;
GO

CREATE ENDPOINT [CustomConnection]
    STATE = STARTED
    AS TCP
(
            LISTENER_PORT = 1500,
            LISTENER_IP = ALL
)
    FOR TSQL();
GO

When you create a new Transact-SQL endpoint, connect permissions for public are revoked for the default TDS endpoint. If access to the public group is needed for the default endpoint, reapply this permission by using the GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]; statement.

Grant access to the endpoint

Issue the following statement to grant access to the CustomConnection endpoint to the SQLSupport group in the corp domain.

GRANT CONNECT
    ON ENDPOINT::[CustomConnection] TO [corp\SQLSupport];
GO

Configure the SQL Server Database Engine to listen on another TCP port

  1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then select Protocols for<instance_name>.

  2. Expand Protocols for<instance_name>, and then select TCP/IP.

  3. In the right pane, right-click each disabled IP address that you want to enable, and then select Enable.

  4. Right-click IPAll, and then select Properties.

  5. In the TCP Port box, type the ports that you want the Database Engine to listen on, separated by commas. In our example, if the default port 1433 is listed, type ,1500 so the box reads 1433,1500, and then select OK.

    Note

    If you aren't enabling the port on all IP addresses, configure the other port in the property box for only for the desired address. Then, in the console pane, right-click TCP/IP, select Properties, and in the Listen All box, select No.

  6. In the left pane, select SQL Server Services.

  7. In the right pane, right-click SQL Server<instance_name>, and then select Restart.

    When the Database Engine restarts, the Error log lists the ports on which SQL Server is listening.

Connect to the new endpoint

Issue the following statement to connect to the CustomConnection endpoint of the default instance of SQL Server on the server named ACCT, using a trusted connection, and assuming the user is a member of the [corp\SQLSupport] group.

sqlcmd -SACCT,1500

Related content

  • Last updated on