Chapter 8 - Configuring Proxy Server for Replication Across the Internet

  • Article

Replication is an important and powerful technology for distributing data and stored procedures across an enterprise. The replication technology in Microsoft SQL Server allows you to make copies of data, move those copies to different locations, and synchronize the data automatically so that all copies have the same data values. Replication can be implemented between databases on the same server or between different servers connected by LANs, WANs, or the Internet.

By combining Microsoft SQL Server with Microsoft Proxy Server, you can replicate data over the Internet without compromising the security of your database. The steps involved in implementing replication over the Internet are configuring the network topology, understanding the security methodology, configuring Microsoft Proxy Server, and configuring SQL Server 7.0 for replication.

Configuring Network Topology

Configuring the network topology is the first step in defining how SQL Server and Microsoft Proxy Server will work together. As shown in the following illustration, Microsoft Proxy Server provides a gateway between the Internet and the internal network, which includes the server running SQL Server 7.0. The server running SQL Server is configured to be both a Publisher and a Distributor. A second server running SQL Server 7.0 and accessible on the Internet is configured as a pull Subscriber.

Cc966466.sqc09001(en-us,TechNet.10).gif

The software requirements for the proxy server are:

  • Microsoft Windows NT 4.0 with Service Pack 4 

  • Microsoft Windows NT 4.0 Option Pack 

  • Microsoft Internet Information Services 4.0 (IIS) 

  • Microsoft Proxy Server 2.0 (MSP) 

The software requirements for the server running SQL Server are:

  • Microsoft Windows NT Server 4.0 with Service Pack 4 

  • Microsoft Internet Explorer 4.01 Service Pack 1 

  • Microsoft SQL Server 7.0 

The proxy server is configured as a multihomed server to prevent unauthorized users on the Internet from accessing the internal server running SQL Server.

A multihomed server is created by using two network interface cards (NICs). The first NIC, called the external proxy interface, isolates the Internet traffic from the internal network. The internal network is accessed through the second NIC, called the internal proxy interface.

This special Internet configuration does not affect the basic security properties of SQL Server. Users with appropriate permissions on the internal network can access data on SQL Server. Users on the Internet with appropriate permissions to SQL Server must have a valid account on the Proxy Server and be authenticated prior to gaining access to any data on SQL Server.

Using Subnets

Do not specify a default gateway for the internal subnets of either the server running SQL Server or the proxy server. The default gateway of the external interface must point to its respective router's IP address.

Security Overview

Security considerations are an important part of the design and implementation of your distributed application. Because replication applies data changes from one server to many others across the network, understanding the layers of network security is essential.

The decentralized availability of replicated data increases the complexity of managing or restricting access to that data. Microsoft SQL Server replication uses a combination of security mechanisms to protect the data and business logic in your application.

One way to consider security requirements is to view the requirements as different layers of access. Each lower layer must work properly before any successive layer is added. Each successive layer is dependent on the proper operation of any preceding layer. These are the four layers of security to configure:

  • Windows NT user accounts 

  • Proxy Server security 

  • SQL Server Agent account access 

  • SQL Server replication account security 

Windows NT User Accounts

The first step in replicating data over the Internet is to establish a connection between the Subscriber and the proxy server. This process requires that Windows NT user accounts be configured on each computer. These accounts should differ from those used to log on to the Windows NT Server or to start SQL Server; otherwise, users might gain administrative access to the Windows NT Server or to SQL Server.

Cc966466.sqc09002(en-us,TechNet.10).gif 

Windows NT Subscriber Account

You must set up a Windows NT user account to start SQL Server Agent. If your organization uses SQL Server Agent services, such as alerts and SQL Mail, and has not established a separate account for SQL Server Agent, the new account must be configured with the minimum access rights required by these services.

Windows NT Proxy Server Accounts

The SQL Server Agent account on the Subscriber must be configured as a user account on the proxy server. This account is entered in the WinSock service on the proxy server to authenticate the Subscriber's request for a connection. You either can use the same account or create a new account for FTP service authentication of the Subscriber. If you are creating separate accounts, the WinSock Proxy user account does not need any special access rights to the proxy server. The FTP user account must have read permission granted so the Subscriber can retrieve data from the snapshot folder.

Proxy Server Security

After the user accounts have been configured, they must be entered into the appropriate services on the proxy server. Microsoft Proxy Server provides a line of defense against unauthorized connections to the internal network. The Distribution Agent or Merge Agent on the Subscriber uses the user account information in the SQL Server Agent account profile to gain access to the FTP and WinSock proxy services on the proxy server. If you have configured a separate user account for the FTP service on the proxy server, the Distribution Agent or Merge Agent accesses the MSsubscription_properties table in the Subscription database to retrieve the FTP user account name and password.

When authentication is completed, the Subscriber gains access to SQL Server on port 1433 configured in the WinSock Proxy service. A logon to SQL Server is not established at this point. When the initial connection is made, only port 1433 is active. No access is granted to the FTP port 21. The Distributor server must validate the Subscriber and the subscription as well as identify the type of replication requested. Only then is a connection made to the FTP port.

Cc966466.sqc09003(en-us,TechNet.10).gif

Configuring SQL Server Agent Account Access

After proxy server security has been established, access to all the servers required for replication has been gained. Permission has been granted to port 1433, which allows the Subscriber to initiate a direct connection to SQL Server. Using WinSock port 1433, the Subscriber establishes a connection to the Distributor server for transaction replication and to both the Distributor and Publisher servers for merge replication. You must ensure that a user account has been configured to allow the Subscriber to log in to the Publication database. This user account should not be granted any permissions, but is required for the replication process to work properly. If you are running separate Publisher and Distributor servers, you must configure a logon account on each server.

SQL Server Replication Login Account

For pull subscriptions, replication requires that the Publisher server be registered on the Subscriber. The registration process requires a username and password to gain access to the SQL Server database on the Publisher server. The ability to register the Publisher server on the Subscriber means that you have established a communications link at the Windows NT level and at the SQL Server level. If you are not able to register the Publisher on the Subscriber, you must check each user account to ensure they have been granted permission to access Windows NT and SQL Server. For more information about registering SQL Server, see SQL Server Books Online.

An existing Windows NT or SQL Server login account must be used to register the Publisher server on the Subscriber. This account does not need to be granted special access rights on SQL Server except to enter the Publication database. Access rights can be as a guest or by explicitly adding this user to the database. This account must also be included in the Publication Access List (PAL) of each publication you want to grant subscription permission to the Subscriber.

Cc966466.sqc09004(en-us,TechNet.10).gif

Publication Access

This is the final security check prior to exposing data. The login used by the Replication Agent is validated against the PAL of each publication it tries to access. If the Subscriber's login is not found in the PAL, access is denied. Using separate logins for different Subscribers in the PAL can help limit access to data in the publication.

Cc966466.sqc09005(en-us,TechNet.10).gif

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.

Configuring SQL Server

To configure Internet replication, key parameters must be set on both the Publisher and Subscriber. SQL Server must be configured to interoperate with the FTP and WinSock Proxy services on the proxy server. There are four key steps to configuring SQL Server replication:

  1. Configure SQL Server to work with Microsoft Proxy Server. 

  2. Configure SQL Server to work with the FTP service on the proxy server. 

  3. Configure a SQL Server Subscriber for the Internet. 

  4. Verify SQL Server is working with Microsoft Proxy Server. 

Configuring SQL Server to Work with Proxy Server

After Microsoft Proxy Server has been installed on the proxy server, you must configure the server running SQL Server to be a proxy client. This requires binding SQL Server to a WinSock port to allow communication over the Internet.

Binding a WinSock Port for Data Communications

Configuring SQL Server to work with Microsoft Proxy Server requires changes to the file \Mssql7\Binn\Wspcfg.ini on the server running SQL Server. If the file does not exist, create it by using Notepad. The file must contain these entries:

[sqlservr]
ServerBindTcpPorts=1433
Persistent=1
KillOldSession=1

SQL Server listens on service port 1433. When a user or anonymous subscriber is authenticated by SQL Server, a session is established between the user or anonymous subscriber and SQL Server using service port 1433.

Configuring SQL Server as a Proxy Client

When the server running Microsoft Proxy Server is set up, it creates a share called mspclnt that points to the directory C:\Msp\Clients. You must connect to this share and run the proxy client configuration utility (mpclnt) to establish SQL Server as a proxy client. After setup is complete, SQL Server is an internal client to the proxy server.

To run the Proxy Client configuration utility

  1. From the server running SQL Server, click Start, and then click Run.

  2. Under Open type: \\ servername \mspclnt, and run the Proxy Server client setup program.

  3. When setup is complete, restart the SQL Server server for the changes to take effect. 

Configuring SQL Server to Work with the FTP Service

Before you can publish articles over the Internet, the Publisher and Distributor must be enabled to listen on either the TCP/IP or the Multiprotocol network protocol. Microsoft SQL Server uses TCP/IP Sockets or Multiprotocol Net-Libraries over TCP/IP to establish the initial ODBC connection between the Distributor on one side of the Internet and the Subscriber on the other. TCP/IP Sockets Net-Library is enabled during the default SQL Server setup, but may not be enabled if you did a custom installation.

To enable access control for SQL Server

  1. On the Start menu, click Programs/MS SQL Server 7.0, and then click Client Network Utility. 

  2. In the SQL Server Client Network Utility dialog box, on the General tab, click TCP/IP in the list. 

  3. Click Add. 

  4. At Server alias, enter the protocol name defined for the WinSock Proxy Service above. 

    Note There is no requirement to use the same name for the Server Alias and the Protocol Name created in WinSock Proxy.

  5. At Network libraries, ensure that the TCP/IP option is selected. 

  6. At Connection parameters, ensure that port 1433 appears. 

SQL Server must be informed of the FTP home directory location on the proxy server. The snapshot folder (by default \Mssql7\Repldata\Ftp) is redirected to the FTP home directory on the proxy server, enabling data to be transferred to the Subscriber. A new snapshot folder is set up during the FTP service configuration.

To set the FTP home directory

  1. On the Start menu, point to Programs/Microsoft SQL Server 7.0, and then click Enterprise Manager. 

  2. Expand SQL Server Group, and then click the Publication Server. 

  3. On the Tools menu, choose Wizards. 

  4. In the Select Wizard dialog box, expand Replication. 

  5. Click Configure Publishing and Distribution Wizard. 

  6. Click the Publishers tab. 

  7. Double click the Publishing server that will be placing files into the snapshot folder. 

  8. Enter the UNC path name \\ ProxyServerName \Repldata\ftp, and then click By impersonating the SQL Server Agent Account on PublishingServername (Trusted connection). 

Configuring a SQL Server Subscriber for the Internet

To ensure the Subscriber can access information on the Publisher by using Microsoft Proxy Server, a pull subscription must be configured with information that helps it resolve the address of the Publisher.

Note For a Subscriber to subscribe to a Publication over the Internet, the Publication on the Publisher must have the Allow Snapshots to be downloaded using FTP option enabled.

To configure a pull subscription

  1. In SQL Server Enterprise Manager, click the subscribing server. 

  2. On the Tools menu, point to Replication, and then click Pull Subscriptions to SubscriptionServerName . 

  3. Click Pull New Subscription. 

    The Pull Subscription Wizard starts. 

  4. When the Choose Publication screen appears, click the publishing server. If the publishing server does not appear in the list, click the Register Server button and register the Publisher. 

  5. At Specify Synchronization Agent Login, enter an account with appropriate security settings. 

  6. At Choose Destination Database, select an existing database in the list or click New Database to configure a new Subscription database. 

  7. At Initialize Subscription, select Yes, initialize schema and data at the Subscriber. 

  8. If the Snapshot Delivery option appears, select Yes, use FTP to copy the Snapshot files. 

  9. At Set Distribution Agent Schedule, select an appropriate scheduling scheme. 

  10. At Allow Anonymous Subscriptions, select Yes, make the Subscriptions anonymous. 

  11. Continue through the remaining sections of the wizard, and then click Finish. 

  12. When the Pull Subscriptions to SubscriptionServerName dialog box appears, click Properties. 

  13. When the Pull Subscription Properties - PublisherName:databasename:replicationtype dialog box appears, click Snapshot Delivery. 

  14. Ensure the Use File Transfer Protocol (FTP) check box is selected. 

  15. At FTP parameters for Server address of the Distributor, enter the IP address of the NIC card that connects to the Internet on the Proxy Server used to interface with the Publisher. 

  16. At Port enter the FTP port number of the Publisher's Proxy Server connection (usually port 21). 

  17. At Login, enter an account that has been configured with appropriate security clearance. 

  18. At Password enter the password configured for this account. 

  19. Click OK, and then click Close. 

Verifying SQL Server is Working with Proxy Server

To verify that a connection to Proxy Server has been established through port 1433, check the WinSock Proxy service on the proxy server. You should see a session in Proxy Server for the user account that SQL Server is running under.

The user account may not appear immediately. Allow SQL Server a few moments and click the Refresh button periodically. If the account for SQL Server Agent does not appear in the list, stop and restart SQL Server. SQL Server is a client to the proxy server and the service account in which SQL Server was configured to run should appear in the list.

Note You must use a fully qualified name; computer accounts will not work.

To verify a connection

  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. Click WinSock Proxy Service, and then select Properties. 

  4. On the Services tab, click Current Sessions. 

  5. Click WinSock Proxy service. 

Alternatively, you can verify your IP connection and port information on any of the computers by using the netstat command. The following is output from the SQL Server server using netstat –a command. This lists both client- and server-side connections as well as their status and port numbers. Use netstat without any options to client sessions only. If you want to display only ports 1433 and 21, use netstat –an to display TCP connections only.

You may notice that several ports other than 1433 are listed. Keep in mind that 1433 is configured as the incoming port. The outgoing port is dynamic and Proxy Server assigns it when a connection is established. The dynamic port ranges between 1025 and 5000.

Active Connections

Proto Local Address Foreign Address State
TCP SQLReplServer:1026 0.0.0.0:0 LISTENING
TCP SQLReplServer:1031 0.0.0.0:0 LISTENING
TCP SQLReplServer:1033 0.0.0.0:0 LISTENING
TCP SQLReplServer:ftp 0.0.0.0:0 LISTENING
TCP SQLReplServer:1058 0.0.0.0:0 LISTENING
TCP SQLReplServer:1059 0.0.0.0:0 LISTENING
TCP SQLReplServer:135 0.0.0.0:0 LISTENING
TCP SQLReplServer:135 0.0.0.0:0 LISTENING
TCP SQLReplServer:1433 0.0.0.0:0 LISTENING
TCP SQLReplServer:1025 0.0.0.0:0 LISTENING
TCP SQLReplServer:1025 localhost:1026 ESTABLISHED
TCP SQLReplServer:1026 localhost:1025 ESTABLISHED
TCP SQLReplServer:1029 0.0.0.0:0 LISTENING
TCP SQLReplServer:1030 0.0.0.0:0 LISTENING
TCP SQLReplServer:1032 0.0.0.0:0 LISTENING
TCP SQLReplServer:1056 0.0.0.0:0 LISTENING
TCP SQLReplServer:1057 0.0.0.0:0 LISTENING
TCP SQLReplServer:137 0.0.0.0:0 LISTENING
TCP SQLReplServer:138 0.0.0.0:0 LISTENING
TCP SQLReplServer:nbsession 0.0.0.0:0 LISTENING
UDP SQLReplServer:1059 *:* 
UDP SQLReplServer:1088 *:* 
UDP SQLReplServer:135 *:* 
UDP SQLReplServer:nbname *:* 
UDP SQLReplServer:nbdatagram *:*