Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

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 2005 Supports NUMA.
ms189310.note(en-US,SQL.90).gifNote:
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.
  1. 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
    
  1. 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
    
  1. In SQL Server Configuration Manager, expand SQL Server 2005 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.

    ms189310.note(en-US,SQL.90).gifNote:
    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 2005 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.

  1. 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
    
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.