Secure the Publisher

Article
03/08/2017

The following replication agents connect to the Publisher:

Log Reader Agent
Snapshot Agent
Queue Reader Agent
Merge Agent

We recommend that you provide an appropriate login for these agents, follow the principle of granting the minimal rights that are required, and protect the storage of all passwords. For information about the permissions that are required for each agent, see Replication Agent Security Model.

Besides appropriately managing logins and passwords, you should understand the role of the publication access list (PAL). The PAL is used to enable logins to access to publication data while restricting ad hoc access to the database at the Publisher.

Publication Access List

The PAL is the primary mechanism for securing publications at the Publisher. The PAL functions similarly to a Microsoft Windows access control list. When you create a publication, replication creates a PAL for the publication. The PAL can be configured to contain a list of logins and groups that are granted access to the publication. When an agent connects to the Publisher or Distributor and requests access to a publication, the authentication information in the PAL is compared to the Publisher login that the agent provides. This process provides additional security for the Publisher by preventing the Publisher and Distributor login from being used by a client tool to perform modifications on the Publisher directly.

Note

Replication creates a role on the Publisher for each publication to enforce PAL membership. The role has a name in the form Msmerge_<PublicationID> for merge replication and MSReplPAL_<PublicationDatabaseID>_<PublicationID> for transactional and snapshot replication.

By default, the following logins are included in the PAL: the members of the sysadmin fixed server role at the time the publication is created and the login that is used to create the publication. By default, all logins that are members of the sysadmin fixed server role or the db_owner fixed database role on the publication database can subscribe to a publication without being explicitly added to the PAL.

When you are using the PAL, consider the following guidelines:

You must associate the SQL Server login with a database user in the publication database before adding the login to the PAL.
Follow the principle of least privilege by allowing logins in the PAL only the permissions the logins must have to perform replication tasks. Do not add the logins to any fixed database roles or server roles that are not required for replication. For more information about the permissions that are required, see Replication Agent Security Model and Replication Security Best Practices.
If a remote Distributor is used, accounts in the PAL must be available at both the Publisher and the Distributor. The account must be either a domain account or a local account that is defined at both servers. The passwords associated with both logins must be the same.
If the PAL contains Windows accounts and the domain uses Active Directory, the account under which SQL Server runs must have permissions to read from Active Directory. If you experience issues with Windows accounts, make sure that the account under which SQL Server runs has sufficient permissions. For more information, see the Windows documentation.

To manage the PAL, see Manage Logins in the Publication Access List.

Snapshot Agent

There is one Snapshot Agent for each publication. For more information, see Create a Publication.

FTP Snapshot Delivery

If you specify that snapshots should be made available through an FTP share rather than a UNC share, you must specify a login and password when configuring FTP access. For more information, see Deliver a Snapshot Through FTP.

Log Reader Agent

There is one Log Reader Agent for each database published for transactional replication. For more information, see Create a Publication.

Queue Reader Agent

There is one Queue Reader Agent for all Publishers and publications (that allow queued updating subscriptions) associated with a given Distributor. For more information, see Enable Updating Subscriptions for Transactional Publications.

Share via