Replication Agent Security Model

Replication Agent Security Model

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

Replication Agent Security Model

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.

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.

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.

Note:
Replication scripts created from SQL Server 2000 should be upgraded for SQL Server 2008 in order to take advantage of security enhancements. For more information, see How to: Upgrade Replication Scripts (Replication Transact-SQL Programming).

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.

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.

Concepts

Other Resources

Help and Information