Configuring Microsoft Internet Security and Acceleration Server for Microsoft SQL Server 2000 Replication over the Internet

  • Article

By Vijay Tandra Sistla , Michael Blythe

Contributor Adina Hagege

Applies To Microsoft SQL Server 2000 and Internet Security and Acceleration (ISA) Server.

Summary By combining SQL Server with Microsoft Internet Security and Acceleration (ISA) Server, you can replicate data over the Internet in a more secure environment. The steps involved in implementing replication over the Internet include: configuring the network topology, understanding account security, configuring ISA Server, and configuring SQL Server 2000 for replication.

On This Page

Introduction
Configuring the Network Topology
Configuring ISA Server
Configuring the Publisher/Distributor and Subscriber to Work with ISA Server
Registering the Publisher/Distributor on the Subscriber
Validating the ISA Server Configuration
Configuring the FTP Service
Configuring Replication
Conclusion

Introduction

Replication is an important and powerful technology for distributing data and database objects (such as tables, views, and stored procedures) across an enterprise. The replication technology in Microsoft SQL Server 2000 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. For more information about replication and the terminology used to describe how replication works, see SQL Server Books Online.

By combining SQL Server with Microsoft Internet Security and Acceleration (ISA) Server, you can replicate data over the Internet in a more secure environment. The steps involved in implementing replication over the Internet include: configuring the network topology, understanding account security, configuring ISA Server, and configuring SQL Server 2000 for replication.

In order to explain these steps in more detail, this paper relies on a replication topology that has the following characteristics: anonymous pull subscriptions, and snapshot delivery with FTP. As shown in the illustration in the next section, the Publisher uses a local Distributor and is located on an organization's internal network; the Subscriber is located on the Internet. The FTP server is on the same computer as the Publisher/Distributor, but could easily be on a different computer.

It is possible to use different replication configurations with ISA, but these configurations are not described in this paper. It is possible, for example, to use push subscriptions and manual snapshot delivery, with the Publisher located on the Internet and the Subscriber located on an internal network. For more information on push and pull subscriptions, see "Subscribing to Publications" in SQL Server Books Online.

Note: Both SQL Server replication and ISA Server use the term "publishing," but the term refers to different concepts in each technology. Replication uses a publishing industry metaphor to describe the process of distributing data. ISA Server, however, refers to a "publishing rule" as a directive to allow access to a particular resource through a particular service or protocol.

Configuring the Network Topology

Configuring the network topology is the first step in defining how SQL Server and ISA Server work together. The following illustration shows that ISA Server provides a connection between the Internet and an organization's internal server running SQL Server 2000. This internal server is configured to be both a Publisher and a Distributor in the replication topology, and it is also configured to be an FTP server. The illustration shows the Subscriber in the replication topology running SQL Server 2000 and accessible over the Internet.

Figure 1:

Figure 1:

Although ISA Server is used to connect users on the Internet to internal network resources, unauthorized users can be prevented from gaining access to the internal network because ISA Server is configured as a multihomed server. A multihomed server has two network interface cards (NICs) that help to provide a secure means to connect heterogeneous networks. The first NIC (public), connected to the Internet, isolates users on the Internet from the internal network. The internal network has access to the ISA Server through the second NIC (private). The two NICS can only connect when a user or service on the internal network is granted permission by the ISA Server, or when the ISA Server grants a user on the Internet permission to access an internal resource (such as the Publisher in a replication topology).

Further, ISA Server controls which services (such as FTP) on the internal network may complete a connection or bind to a port on the ISA Server. To establish a connection to the internal network, the user on the Internet enters the ISA Server using the external NIC (also called a "listener") and connects to one of the ports (such as port 8080), using a protocol defined for ISA Server. Making a connection to a port on the ISA Server provides access only to services on the ISA Server to which the user has permission. The user will not be able to gain access to any other services or resources on the internal network.

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, users on the Internet must first know the IP address, connect to the appropriate port on the ISA Server, and then provide a valid SQL Server login.

This paper addresses the protocol definitions and publishing rules necessary for replication. For information on installing and configuring ISA Server, see the ISA Server documentation. Minimum software requirements for ISA Server and SQL Server are as follows:

ISA Server:

  • Microsoft Windows 2000 with Service Pack 1 or later

  • ISA Server 2000 with Service Pack 1 or later

SQL Server:

  • Windows NT Server 4.0 with Service Pack 4

  • Microsoft Internet Explorer version 4.01 with Service Pack 2

  • SQL Server 2000

Replication Agent Security Overview

When configuring the network topology, it is important to understand how replication agent security works. The agents (the Distribution Agent for transactional replication and the Merge Agent for merge replication) need access to the Publisher/Distributor and Subscribers in the replication topology. Replication agents can connect to the computers using SQL Server Authentication or Windows Authentication, which affects the user context under which the agents run. In addition to having access to SQL Server, the user account under which the Distribution Agent or Merge Agent connects to the Publisher/Distributor must have read access to the snapshot share, which is an FTP share in this scenario.

When you initially configure replication, you can specify the type of authentication you wish to use (Windows Authentication or SQL Server Authentication). You can also specify the type of authentication as a command line argument to an agent at runtime. For example, choose the security type for connections to a Subscriber by setting the SubscriberSecurityMode property of the Merge Agent or Distribution Agent. For more information on agent login security, see "Agent Login Security" in SQL Server Books Online.

Note: The authentication type can be different for each of the connections made in replication. For example, the Merge Agent connects to the Publisher, the Distributor, and the Subscribers. The connection to the Distributor and Publisher could use Windows Authentication and the connection to each Subscriber could use SQL Server Authentication.

When SQL Server Authentication is used, the user context under which an agent runs can be specified as a command line argument to an agent at runtime or can be specified when replication is configured. For example, choose an account under which the Merge Agent or Distribution Agent runs when it makes a connection to a Subscriber by setting the -SubscriberLogin property.

User context cannot be specified in the same way when Windows Authentication is used; replication agents run under the context of the user who launches the agent. The user context is:

  • The SQL Agent account if a replication job is started from SQL Agent (the way in which replication agents are most commonly run).

  • The currently logged-in user if a replication agent is launched from the command line.

  • The application context if a Microsoft ActiveX control for replication is launched from an application.

If a replication agent is configured to make connections using Windows Authentication, the user who launches the agent must have a valid login on the target computer running SQL Server. The login must also be in the publication access list (PAL) for connections to the Publisher or Distributor. For more information, see "Publication Access Lists" in SQL Server Books Online.

Configuring ISA Server

In this scenario, SQL Server uses two methods of access to replicated data: ODBC and FTP. When you are replicating data over the Internet and you are using FTP for snapshot delivery, you must use both of these services. You must first create a server publishing rule so that replication can establish an ODBC connection from the Subscriber to the Publisher/Distributor.

Important: By default, all named instances of SQL Server are assigned TCP/IP ports dynamically. If you are using a named instance of SQL Server you must assign that instance a static port number. Choose the port number now, so you can use it in the following protocol definition. You will associate that port with the named instance in the section "Configuring the Publisher/Distributor with Server Network Utility."

Creating a Protocol Definition for a Named Instance of SQL Server

ISA Server includes a protocol definition, called "Microsoft SQL Server," which uses port 1433 and is appropriate for a default instance of SQL Server. If you are using a named instance of SQL Server, you must create a protocol definition using a different port number.

To create a protocol definition for a named instance of SQL Server

  1. On the Start menu of the ISA Server computer, point to Programs/Microsoft ISA Server/ISA Management.

  2. Expand Internet Security and Acceleration Server, expand Servers and Arrays, and expand the applicable server (or array). Click Policy Elements, right-click Protocol Definitions, click New, and then click Definition. The New Protocol Definition Wizard opens.

  3. On the Welcome page of the wizard, in Protocol Definition Name, type a name for the protocol definition (for example, SQL Named Instance Protocol). Then, click Next.

  4. In Protocol Type, select TCP. In Direction, select Inbound. In Port Number, type the port number you will use for the named instance. Click Next.

  5. Click No. Click Next and then click Finish.

Creating a Server Publishing Rule

ISA Server publishing rules are the directives that actually make the SQL Server publication available to specific external clients (as mentioned in the introduction, ISA Server publishing rules have a different meaning than the term "publishing" in SQL Server replication). The server publishing rule opens up a port on the ISA Server for SQL Server. The process of creating a rule for FTP is nearly identical and is described in the section "Creating a Server Publishing Rule for FTP Services."

To create a server publishing rule for SQL Server

  1. On the Start menu of the ISA Server computer, point to Programs/Microsoft ISA Server/ISA Management.

  2. Expand Internet Security and Acceleration Server, expand Servers and Arrays, expand the applicable server (or array), and expand Publishing. Right-click Server Publishing Rules, click New, and then click Rule. The New server publishing rule Wizard opens.

  3. On the Welcome page of the wizard, type a name for the server publishing rule (for example, SQL Publish). Then, click Next.

  4. In IP address of internal server, type the IP address of the SQL Server computer located on the internal network (the Publisher).

  5. In External IP address on ISA Server, type the IP address of the external NIC of the ISA Server. This IP address should not be in the local address table (LAT) defined for ISA Server. Then, click Next.

  6. In Apply the rule to this protocol, select "Microsoft SQL Server" or the protocol definition you created (for example, "SQL Named Instance Protocol") and click Next.

  7. On the Client Type page, select Any request. (Alternatively, if you want to limit access to the SQL Server to specific Internet clients, select Specific computers.) Click Next and then click Finish.

Configuring the Publisher/Distributor and Subscriber to Work with ISA Server

After ISA Server is configured, you must configure the Publisher/Distributor used for replicating data in SQL Server to work with ISA Server.

SQL Server listens for a connection request on port 1433 (or the appropriate port for a named instance) of the ISA Server. Before you can replicate SQL Server publications 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 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 your installation has been customized.

Configuring the Publisher/Distributor with Server Network Utility

You must configure the Publisher/Distributor to ensure it will hear connection requests coming into the ISA Server.

To configure the Publisher/Distributor with Server Network Utility

  1. On the Start menu of the Publisher/Distributor computer, point to Programs/Microsoft SQL Server, and then click Server Network Utility.

  2. In the Server Network Utility dialog box, select the appropriate instance from the Instance(s) on this server list.

  3. On the General tab, ensure that TCP/IP is listed in the Enabled protocols list. If it is not, select TCP/IP from the Disabled protocols list, and click the Enable>> button.

  4. Select TCP/IP in the Enabled Protocols list and click Properties. Enter 1433 for DefaultPort (or the appropriate port for a named instance). Click OK.

  5. Select Enable Winsock Proxy. Enter the IP address of the external NIC of the ISA Server in the Winsock proxy address text box and port number 1433 (or the appropriate port for a named instance) in the Winsock proxy port text box.

  6. Click OK to complete the changes.

  7. If changes were required, you must stop and restart SQL Server for the changes to take effect.

Configuring the Subscriber with Client Network Utility

The Subscriber must be informed of the port the Publisher will be listening on and the network protocol used to establish a communications link.

Configuring the Subscriber with Client Network Utility

  1. On the Start menu of the Subscriber computer, point to Programs/Microsoft SQL Server, and then click Client Network Utility.

  2. In the Client Network Utility dialog box, on the Alias tab, click Add.

  3. Select TCP/IP in the Network libraries list.

  4. Enter a server alias in the Server alias text box and a server name in the Server name text box. The server alias should be the same as the value returned by SERVERPROPERTY(ServerName) at the Publisher/Distributor. The server name should be the IP address of the external NIC of the ISA Server.

  5. Clear the Dynamically determine port check box and, in Port number, enter port number 1433 (or the appropriate port for a named instance).

Registering the Publisher/Distributor on the Subscriber

Registering the Publisher/Distributor on the Subscriber ensures that a connection can be made from the Subscriber to the server running SQL Server. This registration is required before any anonymous publications will be exposed in the replication user interface. If the server is not registered, you can still add subscriptions at the Subscriber with stored procedures. 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.

To register the Publisher/Distributor on the Subscriber

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

  2. Right-click SQL Server Group, and then click New SQL Server Registration.

  3. Enter the following information as requested by the Register SQL Server Wizard.

    Wizard Page

    Action

    Select a SQL Server

    Enter the name of the Publisher/Distributor.

    Select an Authentication Mode

    Select an appropriate option for your application.

    Select a connection option

    If you chose SQL Server Authentication on the previous page, enter the name and password the Publisher/Distributor can validate.

    Select SQL Server Group

    Choose an appropriate option for your organization.

Important: For increased security, the login account used to register the Publisher/Distributor server on the Subscriber should be granted no special access rights on SQL Server except to the publication database.

Validating the ISA Server Configuration

  • Before configuring replication, you should test the ISA Server configuration. Attempt to transfer data from the external NIC on the ISA Server to SQL Server by using the SQL Server osql utility on the Subscriber to establish a connection to the Publisher/Distributor. For more information on the osql utility, see SQL Server Books Online.

To verify that a connection to ISA Server has been established through port 1433 (or the appropriate port for a named instance), check the Firewall Service on the ISA Server. You should see a session entry in ISA Server corresponding to the Publisher/Distributor. If you do not see an entry, click Refresh periodically; if it still does not appear, stop and restart SQL Server.

To verify that a connection has been established to ISA Server

  1. On the Start menu of the ISA Server computer, point to Programs/Microsoft ISA Server, and then click ISA Management.

  2. Expand Internet Security and Acceleration Server, expand Servers and Arrays, expand the applicable server (or array), expand Monitoring, and then click Sessions.

  3. In the pane showing details, view the sessions currently running.

Alternatively, you can verify your IP connection and port information on any of the computers by using the netstat command on the command line. 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 see client sessions only. To display only ports 1433 and 21, use netstat an; it displays TCP/IP connections only.

Several ports other than 1433 are listed, but port 1433 is configured as the incoming port (listed under Local Address with a state of LISTENING). The outgoing port is dynamic; ISA Server assigns it when a connection is established. The outgoing port (listed under Foreign Address with a state of ESTABLISHED) 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     *:*

Configuring the FTP Service

Replication can use FTP to transfer initial data and schema from one location to another over the Internet. In the SQL Server replication process, the Snapshot Agent places in the snapshot folder data that is retrieved by the Merge Agent or Distribution Agent on the Subscriber. To locate the snapshot folder, the Subscriber must first establish an ODBC connection to the Publisher/Distributor. Then, the Merge or Distribution Agent on the Subscriber initiates an FTP connection and retrieves any files stored in the snapshot folder.

To configure the FTP service on the Publisher/Distributor, set the FTP home directory to the alternate snapshot folder for the publication. You might need to stop and restart the FTP service for the changes to take effect.

To configure the FTP Service

  1. On the Start menu of the Publisher/Distributor computer, point to Programs/Administrative Tools/Internet Services Manager, and then click FTP Services.

  2. Expand Internet Information Service, and then 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.

    Wizard Page

    Action

    FTP Site Description

    Enter a description. This will appear as the FTP site name in the FTP site tree.

    IP Address and Port Settings

    Select or enter the IP address for this computer in IP Address and enter 21 in TCPPort.

    FTP Site Home Directory

    Enter the directory created by SQL Server: C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA\FTP or a directory you created through Windows Explorer.

    Allow the following

    Select Read and clear Write.

Creating a Server Publishing Rule for the FTP Service

As described in the section on creating an ISA Server publishing rule for SQL Server, server publishing rules are the directives that make a publication available to specific external clients. The scenario in this paper uses FTP for delivery of the initial snapshot; therefore, an ISA Server publishing rule must also be created for FTP services.

To create a server publishing rule for FTP Services

  1. On the Start menu of the ISA Server computer, point to Programs/Microsoft ISA Server/ISA Management.

  2. Expand Internet Security and Acceleration Server, expand Servers and Arrays, and expand the applicable server (or array). Click Publishing, right-click Server Publishing Rules, click New, and then click Rule. The New server publishing rule Wizard opens.

  3. On the Welcome page of the wizard, type in a name for the server publishing rule (for example, FTP Publish). Then, click Next.

  4. In IP address of internal server, type the IP address of the SQL Server computer located on the internal network (the Publisher).

  5. In External IP address on ISA Server, type the IP address of the external NIC of the ISA Server. This IP address should not be in the local address table (LAT) defined for ISA Server. Then, click Next.

  6. In Apply the rule to this protocol, select FTP Server, then click Next.

  7. On the Client Type page, select Any request. (Alternatively, if you want to limit access to the SQL Server to specific Internet clients, select Specific computers.) Click Next and then click Finish.

Configuring Replication

After ISA Server and the Publisher/Distributor and Subscriber computers are properly configured to communicate with each other, you must configure a publication and subscription so that data can be replicated. The easiest approach is to create a publication with a single table, such as the Customers table from the Northwind database (see SQL Server Books Online).

To allow a Subscriber to access data on the Publisher

  1. Create a publication at the Publisher that allows anonymous subscriptions.

  2. Enable the publication for delivery of the snapshot through FTP at the Publisher.

  3. Generate a snapshot for the publication that is written to the FTP directory.

  4. Create an anonymous pull subscription at the Subscriber.

To create a publication that allows anonymous subscriptions

You must select the following options in the Create Publication Wizard.

  • On the Welcome to the Create Publication Wizard page, select Show advanced options in this wizard.

  • On the Customize the Properties of the Publication page, select Yes, I will define filters, enable anonymous subscriptions, or customize other properties.

  • On the Allow Anonymous Subscriptions page, select Yes, allow anonymous subscriptions.

  • On the Set Snapshot Agent Schedule page, clear Create First Snapshot Immediately. You will generate a snapshot after you enable the publication for delivery of the snapshot through FTP.

To enable the publication for delivery of the snapshot through FTP

  1. Access the publication property sheet by clicking Publication Properties at the end of the Create Publication Wizard, or:

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

    2. Expand SQL Server Group, the Publisher server, Replication, and Publications.

    3. Right-click the appropriate publication and select Properties.

  2. On the Snapshot Location tab of the Publication Properties dialog box, select Generate snapshots in the following location.

    Note: If a dialog box appears, click Yes in response to the question Are you sure you want to change this property?

    If you do not want to also generate a snapshot in the default location, clear Generate snapshots in the normal snapshot folder.

  3. Browse to or enter a snapshot folder name in Folder (the default location is C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA\ ) and select Subscriber can access this folder using FTP (File Transfer Protocol).

    Note: \FTP is intentionally omitted from the path C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA\. The snapshot files will be placed in C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA\FTP.

  4. In FTP server name, enter the IP address of the external NIC of the ISA Server, and in Port, enter 21. In Client path to this folder from the FTP root, enter a backward slash (\).

  5. Select a login of anonymous Use an anonymous login or use or use an account with the appropriate rights to log in and read files from the snapshot folder.

  6. Click OK to close the property sheet and OK again to close the informational dialog box.

To generate a snapshot that is written to the FTP directory

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

  2. Expand SQL Server Group, expand the Publisher server, expand Replication Monitor, and expand Agents.

  3. Click on Snapshot Agents and right-click the Snapshot Agent for the publication you created.

  4. Select Start Agent.

The Snapshot Agent will generate files and write them to the snapshot folder. After the agent completes, the Status column should read Succeeded.

To create an anonymous pull subscription that uses FTP for snapshot delivery

You must select the following options in the Pull Subscription Wizard:

  • On the Initialize Subscription page, select Yes, initialize the schema and data.

  • On the Snapshot Delivery page, select Download the snapshot files using File Transfer Protocol (FTP).

  • If the Subscriber is registered at the Publisher, the Allow Anonymous Subscription page will show in the Pull Subscription Wizard. Specify the new subscription as anonymous. If the Subscriber is not registered, the subscription will default to anonymous.

After you have configured replication and published data, you can test the entire configuration by making changes to data at the Publisher and ensuring that those changes are propagated to the Subscriber.

Conclusion

Many companies and organizations require the flexibility of data distribution provided by SQL Server replication and the security rules provided by ISA Server. As this paper demonstrates, it is possible to combine these technologies to provide a more secure and flexible approach to publishing data over the Internet. By defining publishing rules in ISA Server that control access to services and data and help to maintain the security of the Publisher and the internal network, Subscribers can receive snapshots and incremental changes over the Internet.