INF: Replication Setup Over a Firewall

• Microsoft SQL Server, version 6.5

SUMMARY

If you are setting up replication and there is a firewall between the servers involved in replication, the servers need to be set up properly to communicate with each other over the firewall. This article details the steps required to enable the servers to listen on specific ports for Multi-Protocol traffic and to enable the servers to talk to each other on these specific ports.

MORE INFORMATION

Client-Server Communication Over a Firewall

Setting up a client to communicate to a SQL Server over a firewall is a simple three-step process:

1. Make SQL Server listen on a specific port on TCP (the default is 1433) or RPC (the default is a random port greater than 1023). You have to cycle the server after this change.

2. Configure your firewall server to allow traffic on the specific <ip_address><port_number>.

3. Make the client (on the other side of firewall) use the appropriate connection string to talk to the <port_number> on the server. You can also use the Client Configuration Utility to add an "Advanced" entry with the appropriate Net-Library and connection string.

Replication and Secure Network Libraries

Because replication uses trusted connections, it requires a secure Net- Library. Therefore, TCP/IP is not a viable option. Only the Named Pipes or Multi-Protocol (RPC) Net-Library can be used. SQL Server version 6.0 used a random port for the Multi-Protocol Net-Library; SQL Server version 6.5 provides an option to specify a pre-selected port for RPC traffic.

Replication Set Up Over Multi-Protocol Net-Library with a Specific Port

Because replication makes one server act as a client to the other, the steps below must be done on both servers. Before proceeding, note that the following assumptions have been made:

• The names of the servers that have the firewall between them are Server1 and Server2.
• Port 1500 on Server1 and port 2500 on Server2 have been chosen for Multi-Protocol (RPC) traffic. However, note that you are free to choose any port that is not in use (the ports that are in use can be determined by your Windows NT Server administrator. By default, SQL Server uses port 1433 for listening on TCP/IP requests.
• The firewall has enabled both <ip_address_server1><port_1500> and <ip_Address_server2><port_2500>. Contact your network adminstrator to enable specific ports on specific IP Addresses on the firewall server.

1. On Server1, add an entry in the Windows NT registry so that SQL Server listens for incoming connections on the specified port. Use Regedt32.exe and navigate to the following registry key:

         HKEY_LOCAL_MACHINE
            \Software
               \Microsoft
                  \MSSQLSERVER
                     \MSSQLSERVER
                        \RPCNetlib
   
      Add a new value named "RPCprotocols" with a value type of REG_MULTI_SZ
      To the RPCNetlib key. For the value, enter the string
      "ncacn_ip_tcp,1500". Note that there is no space between the comma and
      1500 (the port number). For more information, refer to the "Custom
      Server Configuration Using the Multiprotocol Networking Library"
      section (pages 123 and 124) in the "What's New in SQL Server 6.5" in
      the SQL Server documentation.
   
   

2. On Server2, use the Client Configuration Utility to add the following "Advanced" entries:

       - In the "Server:" field, enter the name of the Server1.
   

       - In the "DLL Name:" field, click the drop down box and select Multi-
         Protocol.
   

       - In the "Connection String:" field, enter
         "ncacn_ip_tcp:<ip_address>[1500]". Note that you must include the
         square brackets around the port number. Contact your network
         administrator to obtain the <ip_address> of Server1.
   

   This connection string forces the replication-related processes in the SQL Executive to use the specified <ip_address> and <port_number> to connect to the specified server. For more information on using the SQL Client Configuration Utility and on Multi-Protocol connection strings, refer to Chapter 4 (pages 88-89) in the "SQL Server Administrator's Companion".

• For step 1, you would set up the registry entry on Server2 and use the <port_number> chosen on Server2 (the example uses port 2500).
• For step 2, you need to use the <ip_address> of Server2, and make sure the correct port is added in the Client Configuration Utility on Server1.