Bagikan melalui

  • Artikel

How to: Configure the Database Engine to Listen on Multiple TCP Ports

When TCP/IP is enabled for SQL Server, the Database Engine will listen 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 will listen on an additional 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). For more information about how to configure a TCP/IP port to a NUMA node, see How SQL Server Supports NUMA.

Note

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.

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 Configuring the Windows Firewall to Allow SQL Server Access.

To 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

To 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

To configure the SQL Server Database Engine to listen on an additional TCP port

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

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

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

  4. Right-click IPAll, and then click 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 click OK.

    Note

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

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

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

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

To 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