Secure the Distributor

Applies to: SQL Server Azure SQL Managed Instance

The following replication agents connect to the Distributor: the Log Reader Agent, Snapshot Agent, Queue Reader Agent, Distribution Agent, and Merge Agent. It is important to provide an appropriate login for each of these agents while following the principle of granting the minimal rights necessary and also protecting the storage of all passwords:

In addition to managing logins and passwords appropriately, it is important to understand the role of the repl_distributor remote server link and the distributor_admin account.

Securing the Connection from the Publisher to the Distributor

To support the communication necessary when administrative stored procedures execute at the Publisher and update information at the Distributor, replication automatically configures the remote server repl_distributor. The repl_distributor remote server entry is used for communication to the distribution database regardless of whether the distribution database is contained within the Publisher instance (a local Distributor) or resides within a remote SQL Server instance (a remote Distributor).

When the distribution database is contained on a local instance, a random password is generated and configured automatically. When the distribution database is located on a remote instance, the administrator configures a shared password during setup of the Publisher and Distributor; this password is then used to provide authentication of traffic that traverses the repl_distributor link.

The Distributor uses the repl_distributor remote server entry to verify that the calling server is configured as a Publisher at the Distributor, validates the password supplied by the Publisher, and validates that the stored procedure is a replication stored procedure during execution.

The password configured for the repl_distributor remote server entry during setup is associated with a SQL Server login, distributor_admin, which is added to the sysadmin fixed server role at the Distributor. The distributor_admin login is used by replication stored procedures when connecting to the Distributor.

Note

Do not change the password for the distributor_admin manually. Always use the sp_changedistributor_password stored procedure, or the Distributor Properties or Update Replication Passwords dialog boxes in SQL Server Management Studio, because password changes are then applied to local publications automatically.

Disabling the distributor_admin login

If the distributor_admin login is disabled at a remote Distributor, you may no longer be able to do the following:

  • Create or delete publications.
  • Change the articles of an existing publication.
  • See the agent status using SQL Server Management Studio (SSMS) or Replication Monitor on the Publisher.
  • Create or delete subscriptions.
  • Post tracer tokens by using Replication Monitor or by executing sys.sp_posttracertoken.
  • Configure a remote Publisher at the Distributor.

As such, disabling the distributor_admin login at a remote Distributor is not recommended. While disabling the distributor_admin login on a local distributor may not impose the same limitations, it is still not a recommended practice.

Snapshot Folder Security

Ensure that the snapshot share has read access granted to the account under which the Merge Agent (for merge replication) or Distribution Agent (for snapshot or transactional replication) runs and write access granted to the account under which the Snapshot Agent runs. For more information about the snapshot folder, see Secure the Snapshot Folder.

See Also

View and Modify Replication Security Settings
Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)
Replication Security Best Practices
View and modify replication security settings