Configuring Proxy Server for SQL Server 7.0 Replication Over the Internet 

 

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)

Contents

Introduction
Configuring Network Topology
Security Overview
Configuring Proxy Server
Configuring the Publisher/Distributor
Configuring the Subscriber for Internet Connectivity

Introduction

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 Network Topology

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.

Network topology

Figure 1. Network topology

The software requirements for the proxy server are:

  • Microsoft Windows NT® version 4.0 with Service Pack 4
  • Windows NT 4.0 Option Pack
  • Microsoft Internet Information Services (IIS) version 4.0
  • Proxy Server version 2.0

The software requirements for the server running SQL Server are:

  • Windows NT Server 4.0 with Service Pack 4
  • Microsoft Internet Explorer version 4.01 with Service Pack 1
  • SQL Server 7.0

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.

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 the IP address of the respective router.

Security Overview

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:

  • Windows NT user accounts
  • Proxy Server security
  • SQL Server replication account security

Windows NT User Accounts

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.

Windows NT Publisher/Distributor account

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.

Publisher

Figure 2. Publisher/Distributor accounts

Windows NT proxy server 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:

  • Be entered in the list for FTP Site Operators.
  • Have permission to log in locally.
  • Have read permission granted to the Snapshot folder

The user account is created automatically and entered into the Mssubscriptions_properties table whenever a new subscription is created.

Windows NT Subscriber account

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.

Proxy Server Security

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:

  • Be an authorized FTP site operator.
  • Have local login capabilities.
  • Have permission to read the snapshot folder on the proxy server.

Default configurations for SQL Server include:

  • binding to port 1433 using Proxy Server WinSock Services
  • binding FTP services to port 21 using Proxy Server Socks Proxy service

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.

Proxy Server security

Figure 3. Proxy Server security

SQL Server Replication Login Account

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.

Server replication login account

Figure 4. SQL Server replication login account

Publication Access

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.

Publication access

Figure 5. Publication access

Configuring Proxy Server

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:

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

Disabling IP Forwarding

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:

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

Configuring the FTP Service

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:

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.
  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.
    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:

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.

  2. Expand Internet Information Service, and then expand the computer name.

  3. Right-click the FTP site defined for replication, and then click 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.

  6. In the Internet Service Manager dialog box, click Yes to continue, and then click the FTP Site tab.

  7. Under Connections, select Limited To, and then enter a maximum number of replication connections.

  8. On 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.

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

Configuring FTP service when using packet filtering

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:

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.
  2. Expand Internet Information Service, and then expand the computer name.
  3. Right-click Web Proxy service, and then click Properties.
  4. On the Service tab, click Security.
  5. On the Packet Filters tab, click Add.
  6. In the Packet Filter Properties dialog box, click Custom filter.
  7. In Protocol ID, select a protocol, and then in Direction, select a direction.
  8. Under Local port, click an appropriate option.
  9. Select the appropriate Local host option to specify the internal computer that exchanges packets with an internal host (Subscriber) computer.
  10. Under Remote host, click Single host, and then enter an IP address. This will allow packet exchange with a specific Internet host computer.

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

Configuring the WinSock Proxy Service

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:

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.

  2. Expand Internet Information Service, and then expand the server running Proxy Server.

  3. Right-click WinSock Proxy service, and then click 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 to the program. It is a mnemonic used to help you identify the socket and the accounts or servers that will be granted access.

  1. At Initial Connection, enter 1433 in the Port text box.

  2. Under Type, select TCP, and then under Direction, select Inbound.

To configure the WinSock Proxy service permissions for a protocol:

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.

  2. Expand Internet Information Service, and then expand the computer name.

  3. Right-click WinSock Proxy service, and then click 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 that is used to start MSSQLServer on the Publisher/Distributor.

To configure the WinSock Proxy service permissions for unlimited access:

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.

  2. Expand Internet Information Service, and then expand the computer name.

  3. Right-click WinSock Proxy service, and then click Properties.

  4. In the WinSock Proxy Service Properties For computername dialog box, click the Permissions tab.

  5. In the Protocol list, select Unlimited Access.

  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 that is used to start MSSQLServer on the Publisher/Distributor.

Validating the Proxy Server Configuration

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.

Configuring the Publisher/Distributor

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:

  1. Configure the Publisher/Distributor to work with Proxy Server.
  2. Register the Publisher/Distributor on the Subscriber.
  3. Configure the Subscriber for Internet connectivity.
  4. Verify SQL Server is working with Proxy Server.

Configuring the Publisher/Distributor to Work with Proxy Server

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.

Binding a WinSock port for data communications

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.

Configuring SQL Server as a proxy client

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:

  1. On the server running SQL Server, click Start, and then click Run.
  2. Under Open, enter \\servername\mspclnt, and then run the Proxy Server Client Setup program.
  3. When Setup is complete, you must restart the server running SQL Server for the changes to take effect.

Redirecting the snapshot folder for the FTP service

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, click Wizards.
  4. In the Select Wizard dialog box, expand Replication.
  5. Click Configure Publishing and Distribution Wizard.
  6. Click the Publishers tab, and then double-click the Publishing server that will be placing files into the snapshot folder.
  7. Enter the UNC path name \\ProxyServerName\Repldata\ftp, and then click By impersonating the SQL Server Agent Account on PublishingServername (Trusted connection).

Configuring the Publisher/Distributor Network Utility

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:

  • TCP/IP Network Library
  • port 1433
  • the external interface IP address of your proxy server

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:

  1. On the Start menu, click Programs/Microsoft SQL Server 7.0, and then click Server Network Utility.
  2. In the SQL Server Network Utility dialog box, on the General tab, click Add.
  3. Under Network Libraries, click TCP/IP. Under Connection Parameters, enter 1433 for Port Number. Under Proxy Address, enter the IP address for the external proxy server interface.
  4. Click OK to complete the changes.

Configuring the Subscriber for Internet Connectivity

Before the Subscriber can establish a connection to the Publisher/Distributor, you must:

  1. Configure the Subscriber to be a client of the Publisher/Distributor. Map the friendly name to the TCP/IP address so the replication agent can resolve the connection over the Internet.
  2. Configure access to the Publisher and FTP services when creating a pull subscription.

Configuring the Subscriber to be a Client of the Publisher

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:

  1. On the Start menu, click Programs/Microsoft 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.

  3. Click Add, and then in Server alias, enter the protocol name defined for the WinSock Proxy Service.

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.

  1. In Network libraries, ensure that TCP/IP is selected.

  2. In Connection parameters, ensure that port 1433 appears.

Creating Pull Subscriptions and Enabling Access to the Publisher and FTP Services

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:

  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 Register Server and register the Publisher.
  5. In Specify Synchronization Agent Login, enter an account with appropriate security settings and exists in the PAL for the publication being subscribed to.
  6. In Choose Destination Database, select an existing database or click New Database to configure a new Subscription database.
  7. In 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. In Set Distribution Agent Schedule, select an appropriate scheduling scheme.
  10. In 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. In 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 connect with the Publisher.
  16. In Port, enter the FTP port number of the Publisher’s Proxy Server connection (usually port 21).
  17. In Login, enter an account that has been configured with appropriate security clearance.
  18. In Password, enter the password configured for this account.
  19. Click OK, and then click Close.

Registering the Publisher/Distributor on the Subscriber

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:

  1. In SQL Server Enterprise Manger, right-click SQL Server Group, and then click New SQL Server Registration.

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

    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.

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 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:

  1. On the Start menu, point to Programs/Microsoft Proxy Server, and then click Microsoft Management Console.
  2. Expand Internet Information Service, and then expand the server running Proxy Server.
  3. Click WinSock Proxy Service, and then click 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 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

© Microsoft Corporation. All rights reserved.