Knowledge Base

HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet

Article ID: 321822

Article Last Modified on 1/10/2006

APPLIES TO

Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2000 64-bit Edition
Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q321822

SUMMARY

Set Up Replication Between Two Computers Running SQL Server

Consider the following two issues when you set up replication between two computers running SQL Server.

Security Context of the Replication Agent

You can configure the replication agent (including Log Reader Agent, the Merge Agent, the Distribution Agent, and the Snapshot Agent):

Impersonate the SQL Server Agent account on the local server (a trusted connection).

-or-

Use SQL Server Authentication.

The executable file for the agent (including Logread.exe and Replmerg.exe) runs under the context of the SQL Server Agent account of the server where the agent is running. The previous two options determine how the replication agents connect to the SQL Server service on the destination server. These options do not control the way that the communication occurs at the operating system level. By understanding that these options do not control communication at this level, you can solve most security issues that occur during replication setup.

Default Client Net-Library

Starting with SQL Server 2000 (Microsoft Data Access Component [MDAC] 2.6), the default client Net-Library is set to TCP/IP. In earlier versions of SQL Server, the default client Net-Library is set to named pipes. If the client computer uses Named Pipes to communicate to the server by using an alias or because the client is running an earlier version of MDAC, and the servers are in trusted domains, the executable file of the replication agent creates a named pipe (a file at the operating system level) on the destination server to establish a connection.

The executable file creates a named pipe by using the security context of the SQL Server Agent account on the source server. If the destination server cannot verify the credentials of this account, the connection is not successful, and you receive the following error message:

SQL server does not exist or access is denied.

For more detailed information about this error message, Microsoft recommends that you try to establish a connection from the source server to the destination server by using a tool such as Isql.exe of the ODBC DSN Wizard. You will receive an operating system-level error message that is similar to one of the following error messages:

10060 - Connection timed out
10061 - Connection refused

You receive these errors if the destination server is in a non-trusted domain or if the servers are stand-alone servers that are separated by the Internet. Named pipe connections cannot be established between two different stand-alone servers across the Internet. To test named pipe connections between two servers, you can use the Makepipe and Readpipe utilities. For more information about the Makepipe and Readpipe utilities, see the "Named Pipes Client Connections" topic in SQL Server Books Online.

If you use TCP/IP, the connection is established by using the Winsock API calls that are made by using the sockets network library. The connection establishes an underlying TCP session by using the TCP three-way handshake protocol. The client computer opens a source port and communicates to a destination port.

Problems If You Use an IP Address to Configure Replication

If you try to register the server (Publisher or the Subscriber) by using an IP address instead of a client alias, or if the client alias is different from the actual SQL Server NetBIOS name, the Merge Agent may fail, and you receive the following error messages:

Error 20084:

The process could not connect to subscriber 'IP address'.
Error 18456:

Login failed for user 'administrator'
The subscription to publication 'test' is invalid.
Error 14010:

The remote server is not defined as a subscription server.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

217395 PRB: Error 18482: "Unable to Connect to Site . . ."
Could not configure [SQL Server Name] as the distributor for [SQL Server Name].
Error 18483:

Could not connect to server [SQL Server Name] because 'distributor_admin' is not defined as a remote login at the server.

To avoid receiving these messages, always create an alias that has the same name as the server that you are trying to register in SQL Server Enterprise Manager.

Set Up Replication Between Two Computers Running SQL Server Across the Internet

Example 1: The Subscriber Has Schema and Data, and the Initial Snapshot Is Not Necessary.

Push Subscription

From a command prompt, ping the Publisher from the Subscriber, and then ping the Subscriber from Publisher to make sure that they can connect to each other.

Name resolution is required for communication to occur between the Publisher and the Subscriber.
In the SQL Server error log on the Subscriber, note the port where the computer running SQL Server is listening.
Use the following information to create a TCP/IP alias for the Subscriber at the Publisher by using Client Network Utility:
- Server alias: Name of the Subscriber
- Net-Library: TCP/IP
- Connection parameters
- Server name: IP address of the Subscriber
- Port number: Port number of the Subscriber (By default, this port number is 1433.)
Request a SQL Server login account to be created on the Subscriber with sufficient permissions in the Subscriber database. For more information about how to configure security, see the following topics in SQL Server 2000 Books Online:
- "Agent Login Security"
- "Connecting to the Distributor"
- "Replication Security"
- "Security Considerations (Location: Replication)"
Register the Subscriber in SQL Server Enterprise Manager on the Publisher by using SQL Server Authentication.
On the Publisher, enable the destination server as a Subscriber. To do so, follow these steps:
1. In SQL Server Enterprise Manager, on the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distribution.
2. Click the Subscribers tab, and then click the properties (...) button.
3. Click the General tab, click Using SQL server authentication of this account to specify that the replication agents use SQL Server authentication, and then add the account information from step 4.

Pull Subscription

To pull the subscription, create an alias for the Publisher at the Subscriber, and then follow these steps:

In SQL Enterprise Manager from the Publisher, on the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distribution.

Note Because the Subscriber is not configured for the distributor, the Configure Publishing, Subscribers, and Distribution options are not available for the Subscriber.

Click the Publishers tab.
Click the Publisher, and then click the Properties (...) button.
Click the General tab, click Using SQL server authentication of this account to specify that the replication agents use SQL Server Authentication, and then specify an account that has sufficient permissions in the publishing database.

Example 2: The Subscriber Does Not Have the Schema and Data, and the Initial Subscription from the Publisher Is Required.

This example requires you to configure the FTP service at the Publisher, and then configure the Subscriber to download the initial snapshot from the FTP site. For more information, see the following topics in SQL Server Books Online:

"Security and Replication Over the Internet"
"Publishing Data Over the Internet Using TCP/IP and FTP"
"Configuring a Publisher or Distributor to Listen on TCP/IP"
"Configuring a Publication to Allow Subscribers to Retrieve Snapshots Using FTP"
"Configuring a Subscription to Use FTP to Retrieve a Snapshot"
"Generating the Initial Snapshot"

Make sure that the default client Net-Library on the server where the replication agent runs is set to TCP/IP.

Use Windows Authentication to Set Up Replication Between Two Computers Running SQL Server in Non-Trusted Domains

If you have to set up replication across two non-trusted domains or workgroups by using Windows authentication, you must configure pass-through authentication. Configure a local Windows account on both the Publisher and the Subscriber that has the same name and password. After you configure this account, use the account to start the SQL Server Agent service on the Publisher for push subscriptions and on the Subscriber for pull subscriptions. Make sure that this account is configured according to the "Setting up Windows Services Accounts" topic in SQL Server Books Online.

If the user tries to make a network connection to a remote computer that is in a non-trusted domain, the logon proceeds as if the user is connecting to an account on the remote computer. The remote computer authenticates the logon credentials against its directory database. If the account is not defined in the directory database, but the guest account on the remote computer is enabled and the guest account has no password set, the user logs on with guest permissions. If the guest account is not enabled, the logon is not successful.

Important In the previous examples, if there is a firewall between the two servers, make sure that the firewall is configured according to the following article:

287932 INF: TCP Ports Needed for Communication to SQL Server Through a Firewall

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

320499 HOW TO: Manually Synchronize Replication Subscriptions by Using Backup or Restore

312292 HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server

299903 FIX: sp_scriptpublicationcustomprocs Generates Replication Stored Procedures

Keywords: kbhowtomaster KB321822

Microsoft KB Archive/321822

Contents