INF: Replication Setup Over a Firewall

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 administrator to enable specific ports on specific IP Addresses on the firewall server.

You will have to enable both servers to listen on the Multi-Protocol Net- Library using the SQL Server Setup Change Network Support option (and cycle the server for this change to take effect). To establish RPC connectivity using specific ports between the two servers, perform the following steps:

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".

This completes the one-way setup of Multi-Protocol communication between the two servers (Server2 talking to Server1). Now, repeat the steps above with relevant <port_number> and <ip_addresses> for Server1 to talk to Server2:

• 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.

This completes the setup for SQL Server replication over a firewall using the Multi-Protocol Net-Library.