Microsoft Corporation
1999
Summary: This article describes combining Microsoft SQL Server with Microsoft Proxy Server to replicate data over the Internet without compromising the security of your database. (20 printed pages)
Introduction
Configuring Network Topology
Security Overview
Configuring Proxy Server
Configuring the Publisher/Distributor
Configuring the Subscriber for Internet Connectivity
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 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 include: configuring the network topology, understanding the security methodology, configuring Proxy Server, and configuring SQL Server version 7.0 for replication.
Configuring the network topology is the first step in defining how SQL Server and Proxy Server will work together. As shown in the following illustration, Proxy Server provides a direct connection between the Internet and an internal server running SQL Server 7.0, identified as the Publisher. The internal server running SQL Server is configured to be both a Publisher and a Distributor. A second server, the Subscriber, is running SQL Server 7.0 and is accessible over the Internet; it is configured as a pull Subscriber.
Figure 1. Network topology
The software requirements for the proxy server are:
The software requirements for the server running SQL Server are:
The proxy server is configured as a multihomed server to prevent unauthorized Internet users from gaining access to internal network resources. A multihomed server consists of two network interface cards (NICs), which provide a secure means to connect homogeneous networks. The first NIC, called the external proxy interface, isolates Internet users from the internal network. The internal network has access to the proxy server through the second NIC, called the internal proxy interface. Connections can be established between the internal proxy interface and the external proxy interface when an internal user is granted permission by the Proxy Server.
Proxy Server controls which services or users on the internal network may complete a connection or bind to a port on the proxy server. This connection is required before any users or servers on the Internet can access data or resources on the internal network.
To establish a connection to the proxy service, the Internet user enters the proxy server using the external proxy interface and connects to one of the ports. Making a connection to a port on the proxy server provides access only to services on the proxy server to which the user has permission, such as a file directory. The user will not be able to gain access to any services or resources on the internal network.
To gain access to resources on the internal network, an internal user or a service with appropriate permissions must connect to a port using the external proxy interface. During the binding process, proxy server authenticates the user and, if there are sufficient permissions, completes the connection.
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 the server running SQL Server. However, to gain access to SQL Server and any publications, Internet users must first know the IP address, connect to the appropriate port on the proxy server, and then provide a valid SQL Server login account.
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 the IP address of the respective router.
Security considerations are an important part of the design and implementation of a distributed application. Because replication applies data changes from one server to many others over 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. 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 three layers of security to configure:
The three servers in the previous illustration must establish connections with each other before replication will work. First, the Publisher/Distributor must make a network connection to the proxy server. Next, the Subscriber must make a SQL Server connection to the Publisher/Distributor, and lastly the Subscriber must make a network connection to the FTP service on the proxy server.
The first step in replicating data over the Internet is to establish a network connection between the Publisher/Distributor and the proxy server. When SQL Server is started, the MSSQLServer service requests permission from Proxy Server to bind to a port using the external proxy interface for its exclusive use. Proxy Server must be able to authenticate the MSSQLServer service account and requires appropriate user account configuration to work properly.
For increased security, the MSSQLServer account on the Publisher/Distributor should not be the same account used to log in to Windows NT.
Figure 2. Publisher/Distributor accounts
The MSSQLServer account on the Publisher/Distributor must be entered in the permissions list for WinSock services on the proxy server. When requesting permission to bind to a port, MSSQLServer submits its account to proxy server for authentication. After the proxy server has authenticated the MSSQLServer account and bound the port, a direct connection from the Internet to the Publisher/Distributor is established.
To access FTP services on the Proxy Server, the replication agent on the Subscriber supplies an account for Proxy Server to authenticate before completing an FTP connection. This account must:
The user account is created automatically and entered into the Mssubscriptions_properties table whenever a new subscription is created.
Before a connection can be established for replication, the Publisher/Distributor server must be registered on the Subscriber. Unless specified otherwise, replication agents will use the same account information used to register the Publisher/Distributor to establish connections.
Note about Integrated Security Do not use an integrated account to register the Publisher/Distributor. Nonintegrated accounts limit access to Publications where permission has been granted.
On a multihomed server, an internal connection must be established from the internal proxy interface to the external proxy interface before any Internet user can gain access to resources on the internal network. Proxy Server monitors internal users or services and permits connections by authorized users only.
As discussed earlier, MSSQLServer service on the Publisher/Distributor establishes a connection to the proxy server and binds to a port that exposes SQL Server to the external proxy interface. Any Internet user who provides the port number, external proxy interface IP address, and a valid login account can gain access to SQL Server.
Replication activities require access to FTP services. When required, the account used by the Subscriber to log in to SQL Server is used to initiate FTP service. By default, this account is the same account that is used to register the Publisher/Distributor server on the Subscriber. However, you can specify another account by entering the account name and password in the MSsubscription_properties table when you create the pull subscription. In either case, the account must:
Default configurations for SQL Server include:
Regardless of the type of replication chosen, an ODBC connection is established from the Subscriber to the Publisher first. The Distributor informs the Subscriber when a connection is required to the FTP services on the proxy server. When prompted by the Distributor, the Subscriber initiates a connection to the FTP service and retrieves any data found in the Snapshot folder.
Figure 3. Proxy Server security
For pull subscriptions, replication requires that the Publisher/Distributor server be registered on the Subscriber. The registration process requires an existing username and password on the Publisher/Distributor to gain access to the publishing database on the Publisher server. The ability to register the Publisher server on the Subscriber means that you have established a network connection at the Windows NT level and at the SQL Server level. If you are not able to register the Publisher/Distributor on the Subscriber, check each connection and the permissions of each account.
The Publisher/Distributor uses the MSSQLServer account to bind a path to WinSock Port 1433 and the external proxy interface. Ensure that the account you are using to register the Publisher/Distributor has sufficient permissions on the Publisher/Distributor.
The login account used to register the Publisher/Distributor server on the Subscriber should be a standard SQL Server login. For increased security, this account should be granted no special access rights on SQL Server except to the Publication database. Access rights to the Publication database can be granted either on a guest basis or by explicitly adding the 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.
Figure 4. SQL Server replication login account
The final level in the security mechanism is to check the user account prior to exposing any publication data. Because a Publisher may have a one-to-many relationship, each Publication can control access through its PAL. 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 can limit access to data in publications.
Figure 5. Publication access
SQL Server uses two methods of access for replicated data: ODBC and FTP. Both services are required for replication over the Internet. Proxy Server must be configured correctly to establish an ODBC link from the Subscriber through the proxy server to the Publisher and Distributor. Before configuring replication, you can test this connection by transferring data from the external proxy interface to SQL Server by using OSQL on the Subscriber to establish a connection to the Publisher/Distributor. Perform a second test by writing data from SQL Server to the redirected snapshot folder on the proxy server. This can be accomplished by logging on to the Publisher/Distributor by using the SQL Agent user account and copying a file from SQL Server to the new snapshot folder.
There are four key steps to configuring Proxy Server:
Although the Internet provides a cost-effective way to publish and collect (pull/subscribe) data over long distances, it also can make the internal network and data vulnerable. To protect the network, disable listening on inbound service ports, and then disable IP forwarding. When IP forwarding is disabled, only the Network Operations-assigned IP address is visible to Internet users, 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 enabled.
To disable IP forwarding:
In some 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.
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 Publisher/Distributor is first established to obtain the location of the snapshot folder. The Merge or Distribution 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 local drop location the Publisher/Distributor will use as the snapshot directory. You must stop and restart the FTP service for the changes to take effect.
To configure the FTP Service:
| 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, for example REPL2, with local logon capability and read permissions to the snapshot folder should be entered as a valid FTP site operator.
To control FTP access through Proxy Server:
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.
If you are using FTP on the Proxy Server with Packet Filtering, FTP access will be totally blocked from the Internet side. When running applications such as FTP on the same server as that is running Proxy Server you need to configure static filters to enable FTP. For more information about Packet Filters, see your Proxy Server documentation.
To add a custom or predefined static filter:
Sample packet filter
FTP Server IN (Filter 1 of 2)
Protocol = TCP
Direction = IN
Local Port = 21
Remote Port = Any
FTP Server OUT (Filter 2 of 2)
Protocol = TCP
Direction = BOTH
Local Port = 20
Remote Port = Any
Before data can be transferred, the Subscriber must initialize an ODBC connection to Publisher/Distributor using the WinSock Proxy service. With WinSock Proxy service, an internal user is responsible for the connection from the external interface to the internal service.
WinSock Proxy service permits the creation of protocols, which provide a way to limit internal access to available ports. It further limits whether the internal user is allowed to initiate an outbound connection or only receive an inbound connection request.
In special situations, certain users may be granted unlimited access to all WinSock Proxy ports. For example, SQL Server may qualify to use unlimited access because, as the user requesting the connection, it is able to initiate or accept a connection request and then authenticate the requestor using its own security service.
Organizations may implement stronger security measures to prevent experienced SQL Server users from initiating or completing WinSock connections by creating a protocol to limit SQL Server access to ports on the proxy server. Using protocols ensures the port is for inbound connections only; internal users cannot establish an unauthorized outbound connection.
To configure a protocol definition for WinSock Proxy to SQL Server:
Note The protocol name has no significance to the program. It is a mnemonic used to help you identify the socket and the accounts or servers that will be granted access.
To configure the WinSock Proxy service permissions for a protocol:
This account is the same user account that is used to start MSSQLServer on the Publisher/Distributor.
To configure the WinSock Proxy service permissions for unlimited access:
This account is the same user account that is used to start MSSQLServer on the Publisher/Distributor.
After all servers are configured, it is recommended that you establish a connection and attempt to transfer data by using OSQL to perform a query. If the servers cannot connect, replication will not function properly.
Before you can publish articles over the Internet, the Publisher/Distributor must be enabled to listen on either the TCP/IP or the Multiprotocol network protocol. SQL Server uses TCP/IP Sockets or Multiprotocol Net-Libraries over TCP/IP to establish the initial ODBC connection between the Publisher/Distributor and the Subscriber. TCP/IP Sockets Net-Library is enabled by default during SQL Server Setup, but may not be enabled if you performed a custom installation.
To configure Internet replication, key parameters must be set on both the Publisher/Distributor 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:
After Proxy Server is installed on the proxy server, you must configure the Publisher/Distributor to be a proxy client. This requires binding SQL Server to a WinSock port to allow communication with the Internet.
Configuring SQL Server to work with 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 using Notepad. The file must contain these entries:
[sqlservr]
ServerBindTcpPorts=1433
Persistent=1
KillOldSession=1
SQL Server listens for a connection request on the proxy server service port 1433. When unlimited access is granted to the MSSQLServer account, any user, service, or anonymous subscriber can request authentication from SQL Server. It is possible to configure a protocol for port 1433 on the proxy server that limits connections to a specific IP address or toinbound requests only.
When the server running Proxy Server is set up, it creates a share named mspclnt, which 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:
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:
To ensure the Publisher/Distributor will hear connection requests coming into the Proxy Server, check for the following entries in the SQL Server Network Utility:
When configuring the SQL Server Network Utility this way, local domain users will no longer be able to access SQL Server using TCP/IP. To provide access to local users, you must configure at least one additional Network Library, such as Named Pipes.
To enable the Publisher/Distributor to listen for connection requests:
Before the Subscriber can establish a connection to the Publisher/Distributor, you must:
The Subscriber must be informed of the WinSock Proxy port the Publisher will be listening on and the network protocol used to establish a communications link.
To enable access control for SQL Server:
Note Using the same name for the Server Alias and the Protocol Name created in WinSock Proxy is not required if you are not using a protocol.
To ensure that the Subscriber can access information on the Publisher using Proxy Server, configure a pull subscription 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 Allow Snapshots to be downloaded using FTP enabled.
To configure a pull subscription:
Registration of the Publisher/Distributor on the Subscriber ensures that a connection can be made from the Subscriber, through Proxy Server, to the server running SQL Server. The registration process also maps the friendly name of the Publisher/Distributor to the IP address so the replication agents can establish a connection over the Internet. For increased security, use a standard SQL login account to register the Publisher/Distributor.
To register the Publisher/Distributor on the Subscriber:
| Select a SQL Server | Enter the name of the Publisher/Distributor |
| Select an Authentication Mode. | Choose SQL Server login. |
| Select a connection option. | Enter the name and password the Publisher/Distributor can validate. |
| Select SQL Server Group. | Choose an appropriate option for your organization. |
Note The SQL login account used to register the Publisher/Distributor is, by default, also used to create a Subscription. This same account is used by the Replication agents to log in to SQL Server, request access to Publications, and gain access to FTP services on the proxy server. You can override the default by supplying a valid Publisher/Distributor login name and password during or after the creation of the Subscription.
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 under which the Publisher/Distributor is running.
The user account may not appear immediately. Allow SQL Server a few moments and then click Refresh 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:
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 server running SQL Server as a Publisher/Distributor using the 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/IP connections only.
Several ports other than 1433 are listed, but port 1433 is configured as the incoming port. The outgoing port is dynamic; Proxy Server assigns it when a connection is established. The dynamic port ranges from 1025 through 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 *:*
--------------------------------------------
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
©1999 Microsoft Corporation. All rights reserved.
Microsoft and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
Other trademarks and tradenames mentioned herein are the property of their respective owners.
Part number: 098-83898