Configuring Proxy Server

Microsoft SQL Server uses two methods of access for replicated data: FTP and ODBC. Both services are required for replication over the Internet. Microsoft Proxy Server must be configured correctly to establish a link through ODBC and then to transfer data. Before configuring SQL Server replication, you must test the proxy server by connecting to it and then transferring data to and from it. There are four key steps to configuring Microsoft Proxy Server:

  1. Disable IP forwarding.
  2. Configure the FTP service.
  3. Configure the WinSock Proxy service.
  4. Validate the Microsoft Proxy Server configuration.

Disabling IP Forwarding

The Internet provides a cost-effective way to publish and collect (pull/subscribe) data over long distances, but it also can make the internal network and data vulnerable. To protect the network, disable listening on inbound service ports and disable IP forwarding. When IP forwarding is disabled, only your Network Operations–assigned IP address is visible to users on the Internet, reducing the potential for unauthorized intrusion.

The proxy server can block external ranges of IP addresses. When IP forwarding is disabled, Internet users cannot initiate connections unless an application service port is specifically enabled.

To disable IP forwarding

  1. In Control Panel, double-click Network.
  2. Click the Protocols tab.
  3. Select TCP/IP, and then click Properties.
  4. In the Microsoft TCP/IP Properties dialog box, click Routing.
  5. Verify that the Enable IP Forwarding check box is cleared.

In large Windows NT environments, you can provide maximum security by establishing a separate domain for your proxy server with a single one-way trust relationship to another domain where SQL Server will be located on your private network. For configuration information, see your Windows NT documentation.

Configuring the FTP Service

SQL Server replication uses FTP to transfer initial data and schema from one location to another over the Internet. The Snapshot Agent places in the snapshot folder data that is retrieved by the Merge Agent or Distribution Agent on the Subscriber.

When SQL Server replication transmits data over the Internet, it uses the FTP directory on the proxy server as the snapshot folder. An ODBC connection to the Distributor is first established to obtain the location of the snapshot folder. The Merge Agent on the Subscriber then initiates an FTP connection to the proxy server and retrieves any information stored in the snapshot folder.

To configure the FTP service on the proxy server, set the FTP home directory to the drop location in which SQL Server data is placed for replication. Stop and restart the FTP service for the changes to take effect.

To configure the FTP Service

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.
  2. Expand Internet Information Service and expand the computer name.
  3. Right-click Default FTP Site.
  4. Point to New, and then click site to start the New FTP Site Wizard.
  5. Enter the following information when requested:
FTP Site Description FTP Replication Site
Select the IP Address to use for this FTP Site Enter the IP address for this site.
TCP Port this FTP Site should use 21
Enter the Path for your home directory For example, C:\repldata\ftp
What access permissions do you want to set for the home directory Allow Read Access

To complete the configuration of the FTP site, alter the default security settings. To increase security, turn off Anonymous Access or guest account access. A user account with appropriate permission should be defined for SQL Server Agent to gain access to all Windows NT servers. This same account is used to gain access to FTP and WinSock Services. For more information about SQL Server Agent account access, see “Configuring SQL Server Agent Account Access” earlier in this chapter.

To control FTP access through Proxy Server

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.
  2. Expand Internet Information Service and expand the computer name.
  3. Right-click the FTP site defined for replication, and then select Properties.
  4. In the site name Properties dialog box, click the Security Accounts tab, and then clear the Allow Anonymous Access check box.
  5. Under FTP Site Operators, grant operator privileges only to the User Accounts that require access to this FTP site. In this case it will be the same account used for SQL Server Agent on the Subscriber.
  6. In the Internet Service Manager dialog box, click Yes to continue.
  7. Click the FTP Site tab.
  8. Under Connections, select Limited To, and then enter a maximum number of replication connections.
  9. Under the Directory Security tab, in the By default, all computers will be: dialog box, click Denied Access.

    Note This excludes any FTP Site Operator account entered in step 5 unless the IP address entered here is a static IP address for that account.

  10. Click Add, and then enter the IP addresses for all replication servers that will be communicating to this server.

Configuring the WinSock Proxy Service

Before data can be transferred, the Subscriber must initialize an ODBC connection by using the WinSock Proxy service.

The WinSock Proxy service requires a protocol definition to identify valid network protocols when access control is enabled. The WinSock Proxy service uses the defined protocols to determine which Windows Sockets applications can be used to access the Internet.

If access control is enabled, the Replication Agent on the Subscriber uses the user account configured for the SQL Server Agent to gain access to system resources. The FTP user account can also be used by the WinSock Proxy for server connections.

To configure a protocol definition for WinSock Proxy to SQL Server

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.
  2. Expand Internet Information Service and expand the server running Proxy Server.
  3. Right-click WinSock Proxy service and select Properties.
  4. In the WinSock Proxy Service Properties For computername dialog box, click Protocols, and then click Add.
  5. In the Protocol name text box, enter a name to identify the server on which the replication Distributor is running.

    Note The protocol name has no significance. It is a mnemonic used to identify the socket and the accounts or servers that will be granted access.

  6. At Initial Connection, enter 1433 in the Port text box.
  7. Under Type select TCP, and under Direction select Inbound.

To configure the WinSock Proxy service permissions

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.
  2. Expand Internet Information Service and expand the computer name.
  3. Right-click WinSock Proxy service and select Properties.
  4. In the WinSock Proxy Service Properties For computername dialog box, click the Permissions tab.
  5. In the Protocol list, select the Protocol name defined when you configured a protocol definition.
  6. Click Edit, and then in the protocol name Permissions dialog box, click Add.
  7. In the Add Users Groups dialog box, select the user account that will provide access to the replication server. This account is the same User Account used for the FTP Service and must configured for the SQL Server Agent account on the Subscriber with appropriate permissions to gain access.

Validating the Proxy Server Configuration

After all servers have been configured, it is recommended that you establish a connection and attempt to transfer data. Using SQL Server Agent user account, test the connection thoroughly. If the servers cannot connect, replication will not function properly.