Agent Login Security

Replication implements login security by requiring a user to have a valid login account and password to connect to a Publisher, Distributor, or Subscriber. Replication agents run under SQL Server Agent and use the associated logins and passwords to connect to the various replication objects and to perform their roles in the synchronization process.

On the Microsoft® Windows® 98 operating system, SQL Server Agent and the replication agents run under the security account of the user logging on to Windows. On the Microsoft Windows NT® 4.0 and Windows 2000 operating system, replication agents run under the login or security context of the SQLServerAgent service. Each agent connects to one or more servers and must have a valid login to complete the connection.

Applying a Snapshot

When applying a snapshot, the agents must have the following capabilities:

• The Snapshot Agent connects to the publication database on the Publisher and to the distribution database on the Distributor. The Snapshot Agent also writes to the snapshot folder when storing the snapshot files.

Transactional Replication

The agents used in transactional replication must have the following capabilities:

• The Log Reader Agent connects to the publication database at the Publisher and to the distribution database at the Distributor.
  
  
• With a push subscription, the Distribution Agent is, by default, located on the Distributor and connects first to the distribution database on the Distributor. While connected to the Distributor, the Distribution Agent connects to the subscription database at the Subscriber. The Distribution Agent also reads from the snapshot folder when applying the snapshot files.
  
  
• With a pull subscription, the Distribution Agent is, by default, located on the Subscriber and connects first to the subscription database on the Subscriber. While connected to the Subscriber, the Distribution Agent connects to the distribution database at the Distributor. The Distribution Agent also reads from the snapshot folder when applying the snapshot files.

Merge Replication

The agents used in merge replication must have the following capabilities:

• With a push subscription, the Merge Agent is located on the Distributor and connects first to the distribution database on the Distributor. While connected to the Distributor, the Merge Agent connects to the subscription database at the Subscriber and then to the publication database at the Publisher. The Merge Agent also reads from the snapshot folder when applying the snapshot files.
  
  
• With a pull subscription, the Merge Agent is located on the Subscriber and connects first to the subscription database on the Subscriber. While connected to the Subscriber, the Merge Agent connects to the distribution database at the Distributor and then to the publication database at the Publisher. The Merge Agent also reads from the snapshot folder when applying the snapshot files.
  
  
• Merge replication requires an entry for the Publisher in the sysservers table at the Subscriber. If the entry does not exist, either SQL Server will attempt to add the entry when you create a merge publication or the Merge Agent will attempt to add the entry. If the login used does not have sufficient access to add the entry in sysservers, an error will be returned.

Note  For an agent that holds simultaneous connections, Microsoft SQL Server™ allows you to configure the login for each connection independently. For example, if the Snapshot Agent connects to the Publisher and to the Distributor, each connection can use a different login.