Replication Agent Security Model

  • Article

The replication agent security model allows for fine-grained control over the accounts under which replication agents run and make connections: A different account can be specified for each agent. For more information about how to specify accounts, see Managing Logins and Passwords in Replication.

Important

When a member of the sysadmin fixed server role configures replication, replication agents can be configured to impersonate the SQL Server Agent account. This is done by not specifying a login and password for a replication agent; however, we do not recommend this approach. Instead, as a security best practice, we recommend that you specify an account for each agent that has the minimum permissions that are described in the section "Permissions That Are Required by Agents" later in this topic.

Replication agents, like all executables, run under the context of a Windows account. The agents make Windows Integrated Security connections by using this account. The account under which the agent runs depends on how the agent is started:

  • Starting the agent from a SQL Server Agent job, the default: When a SQL Server Agent job is used to start a replication agent, the agent runs under the context of an account that you specify when you configure replication. For more information about SQL Server Agent and replication, see the section "Agent Security under SQL Server Agent" later in this topic. For information about the permissions that are required for the account under which SQL Server Agent runs, see Configuring SQL Server Agent.

  • Starting the agent from an MS-DOS command line, either directly or through a script: The agent runs under the context of the account of the user that is running the agent at the command line.

  • Starting the agent from an application that uses Replication Management Objects (RMO) or an ActiveX control: The agent runs under the context of the application that is calling RMO or the ActiveX control.

    Note

    ActiveX controls are deprecated.

We recommend that connections be made under the context of Windows Integrated Security. For backward compatibility, SQL Server Security can also be used. For more information about best practices, see Replication Security Best Practices.

Permissions That Are Required by Agents

The accounts under which agents run and make connections require a variety of permissions. These permissions are described in the following table. We recommend that each agent run under a different Windows account and the account should be granted only the required permissions. For information about the publication access list (PAL), which is relevant for a number of agents, see Securing the Publisher.

Note

User Account Control (UAC) in Windows Vista can prevent administrative access to the snapshot share. You must therefore explicitly grant snapshot share permissions to the Windows accounts that are used by the Snapshot Agent, Distribution Agent, and Merge Agent. You must do this even if the Windows accounts are members of the Administrators group. For more information, see Securing the Snapshot Folder.

Agent Permissions

Snapshot Agent

The Windows account under which the agent runs is used when it makes connections to the Distributor. This account must:

  • At minimum, be a member of the db_owner fixed database role in the distribution database.
  • Have write permissions on the snapshot share.

The account that is used to connect to the Publisher must at minimum be a member of the db_owner fixed database role in the publication database.

Log Reader Agent

The Windows account under which the agent runs is used when it makes connections to the Distributor. This account must at minimum be a member of the db_owner fixed database role in the distribution database.

The account that is used to connect to the Publisher must at minimum be a member of the db_owner fixed database role in the publication database.

Distribution Agent for a push subscription

The Windows account under which the agent runs is used when it makes connections to the Distributor. This account must:

  • At minimum be a member of the db_owner fixed database role in the distribution database.
  • Be a member of the PAL.
  • Have read permissions on the snapshot share.
  • Have read permissions on the installation directory of the OLE DB provider for the Subscriber if the subscription is for a non-SQL Server Subscriber.

The account that is used to connect to the Subscriber must at minimum be a member of the db_owner fixed database role in the subscription database, or have equivalent permissions if the subscription is for a non-SQL Server Subscriber.

Distribution Agent for a pull subscription

The Windows account under which the agent runs is used when it makes connections to the Subscriber. This account must at minimum be a member of the db_owner fixed database role in the subscription database.

The account that is used to connect to the Distributor must:

  • Be a member of the PAL.
  • Have read permissions on the snapshot share.

Merge Agent for a push subscription

The Windows account under which the agent runs is used when it makes connections to the Publisher and Distributor. This account must:

  • At minimum be a member of the db_owner fixed database role in the distribution database.
  • Be a member of the PAL.
  • Be a login that is associated with a user in the publication database.
  • Have read permissions on the snapshot share.

The account used to connect to the Subscriber must at minimum be a member of the db_owner fixed database role in the subscription database.

Merge Agent for a pull subscription

The Windows account under which the agent runs is used when it makes connections to the Subscriber. This account must at minimum be a member of the db_owner fixed database role in the subscription database.

The account that is used to connect to the Publisher and Distributor must:

  • Be a member of the PAL.
  • Be a login associated with a user in the publication database.
  • Be a login associated with a user in the distribution database. The user can be the Guest user.
  • Have read permissions on the snapshot share.

Queue Reader Agent

The Windows account under which the agent runs is used when it makes connections to the Distributor. This account must at minimum be a member of the db_owner fixed database role in the distribution database.

The account that is used to connect to the Publisher must at minimum be a member of the db_owner fixed database role in the publication database.

The account that is used to connect to the Subscriber must at minimum be a member of the db_owner fixed database role in the subscription database.

Agent Security Under SQL Server Agent

When you configure replication by using SQL Server Management Studio, Transact-SQL procedures, or RMO, a SQL Server Agent job is created by default for each agent. Agents then run under the context of a job step, regardless of whether they run continuously, on a schedule, or on demand. You can view these jobs under the Jobs folder in SQL Server Management Studio. The following table lists the job names.

Agent Job name

Snapshot Agent

<Publisher>-<PublicationDatabase>-<Publication>-<integer>

Snapshot Agent for a merge publication partition

Dyn_<Publisher>-<PublicationDatabase>-<Publication>-<GUID>

Log Reader Agent

<Publisher>-<PublicationDatabase>-<integer>

Merge Agent for pull subscriptions

<Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<SubscriptionDatabase>-<integer>

Merge Agent for push subscriptions

<Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<integer>

Distribution Agent for push subscriptions

<Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<integer>1

Distribution Agent for pull subscriptions

<Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<SubscriptionDatabase>-<GUID>2

Distribution Agent for push subscriptions to non-SQL Server Subscribers

<Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<integer>

Queue Reader Agent

[<Distributor>].<integer>

1 For push subscriptions to Oracle publications, the job name is <Publisher>-<Publisher> instead of <Publisher>-<PublicationDatabase>.

2 For pull subscriptions to Oracle publications, the job name is <Publisher>-<DistributionDatabase> instead of <Publisher>-<PublicationDatabase>.

When you configure replication, you specify accounts under which agents should run. However, all job steps run under the security context of a proxy; therefore, replication performs the following mappings internally for the agent accounts that you specify:

  • The account is first mapped to a credential by using the Transact-SQL CREATE CREDENTIAL statement. SQL Server Agent proxies use credentials to store information about Windows user accounts.
  • The sp_add_proxy stored procedure is called, and the credential is used to create a proxy. For more information about proxies, see Creating SQL Server Agent Proxies.

Note

This information is provided to help you understand what is involved in running agents with the appropriate security context. You should not have to interact directly with the credentials or proxies that have been created.

See Also

Concepts

Replication Security Best Practices
Securing the Snapshot Folder

Other Resources

Security Considerations for Replication

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
Changed content:
  • Added a note about snapshot folder requirements specific to Windows Vista.