SQL Server 2000 Security – Part 9 – Replication Security

The previous
of our series served as the introduction for discussion about
replication security, which is the topic of this article. Last time, we
discussed basic terminology, replication types and their agent-based
operations. You should appreciate at this point, the level of complexity
associated with designing, configuring, and managing the replication process.
As you can imagine, dealing with replication security is also a challenging
task that needs to be carefully planned and implemented.

Let’s start with noting that creation and administration of publishers,
distributors, and subscribers (including enabling a database for replication)
is limited to members of the sysadmin server role. Once replication
participants are defined, only members of sysadmin and db_owner fixed database
role on databases enabled for replication can create and configure publications.
Monitoring replication is restricted to members of sysadmin server role and replmonitor
fixed database role, created automatically in the distribution database on the

Distributor is designated on the publisher, typically using the "Configure
Publishing, Subscribers and Distribution…" option in the Tools ->
Replication menu item in the SQL Server Enterprise Manager. You can have
publisher and distributor reside on the same computer, or you can use a remote
distributor. In the latter case, the publisher must be enabled on the target
distributor first. This is done on the Publishers tab of the Publisher and
Distributor Properties dialog box, which is invoked by selecting
"Configuring Publication, Subscribers and Distribution…" option.
From there, you can select the publishers (from the list of the servers
registered with SQL Server Enterprise Manager) that are allowed to use the
currently managed server as the remote distributor. On the Distributor tab in
the same dialog box, you can specify the Administrative link password that
needs to be provided by publishers when connecting to the local distributor in
order to perform replication-related administrative operations. On the same tabs,
you have an option to create multiple distribution databases dedicated to
designated set of publishers, (which increases security in environments where
multiple publishers share the same distributor). Enabling publishers requires
that you specify a login that replication agents will be using to access the
publisher. Similarly, when enabling subscribers from the Subscribers tab on the
Publisher and Distributor Properties dialog box, you will need to set the
security context for Agent connections to them. Since replication agents are
created as SQL Server Agent jobs, they operate by default in the security
context of the SQL Server Agent account. You can either accept the default or
configure them to connect to SQL Server via an arbitrarily assigned SQL Server
login, providing that SQL Authentication is enabled. When defining
publications, you are given a choice to allow anonymous subscriptions. While
this simplifies the management of a publication, especially when the number of
subscribers is large, it negatively affects security of the published data, so
consider its use very carefully.

Now, let’s review security requirements from the point of view of different
replication types. We will start by looking at commonalities between them. One
of them is the snapshot functionality, which, as we already explained in our
introductory article, takes place in each type of replication at least once. Snapshots
are handled by the Snapshot Agent running on the distributor, which creates
several security implications:

    Snapshot Agent account requires a valid login on the publisher
    server and SELECT permissions on the replicated publication in the source
    database. The best way to accomplish this, (in case publisher and distributor
    are hosted on separated computers), is to use SQL Server Agent on the
    distributor, (in which security context Snapshot agent operates on Windows NT
    4.0, 2000, and later systems) a non-privileged domain account, create
    corresponding Windows login on the SQL Server hosting the publisher, and grant
    it appropriate permissions within the database containing the publication.

    Snapshot Agent copies files with replicated data and scripts
    defining publication articles created on a publisher to a network share
    designated as the snapshot repository on the distributor. This location can be
    set in a number of ways. If the publisher and distributor reside on the same
    system, you are prompted for it on the Specify Snapshot Folder page of
    Configure Publishing and Distribution Wizard. If they are located on separate
    computers, then you need to specify the Snapshot folder location on the
    Publisher Properties dialog box (in addition to security context of the
    replication agent connections) when enabling publishers on the target
    distributor (as explained previously). In the same dialog box, you can decide
    whether the publisher will require a password to establish a link to the
    distributor. This password is assigned to a distributor_admin SQL login account
    (which is a member of sysadmin role). This account is created automatically
    when replication is first configured. You should ensure that the password meets
    basic complexity requirements. If you intend to change it, use the sp_changedistributor_password
    system stored procedure or option on the Distributor tab in Publisher and
    Distributor Properties dialog box. While it is possible to configure the
    publisher as trusted, which allows connections without setting a password, such
    configuration is strongly discouraged.

    You can also configure properties of distributor
    and publisher when creating a publication using the Create Publication Wizard
    or managing it from Create and Manage Publications dialog box. The two wizards
    are available from the Tools -> Wizards menu in the Replication container,
    or by right clicking the Replication container and selecting the Configure
    Publishing, Subscribers and Distribution option. You can also modify the
    settings afterwards from the Snapshot Location tab on the Publication
    properties dialog box. Regardless of the method, the folder points by default
    to Program FilesMicrosoft SQL ServerMSSQLRepldata on the distributor.

    The security-related problem results from the fact
    that, by default, this folder is accessed via hidden C$ administrative share
    i.e. \distributor_serverC$Program FilesMicrosoft SQL ServerMSSQLRepldata,
    which is restricted to members of the local Administrators group on the
    distributor server system. This, in turn, requires that the SQL Server Agent
    Service account is added to this group and, in effect, operates with elevated
    privileges, which constitutes a potential vulnerability. You can avoid it by
    configuring SQL Server Agent to run in the security context of a non-privileged
    account, (for more information on this topic, refer to one of
    our earlier articles in this series
    ), and altering the default location and
    share designation for the snapshot folder, (by creating a target folder and share
    on the distribution server and pointing to it from the Snapshot Location tab on
    the Publication properties dialog box). Make sure that access to this folder is
    restricted to the account under which the SQL Server Agent on the distributor
    operates. Note also that each publisher can be configured with a different
    snapshot folder (configurable from the Publisher properties dialog box on the

    New in SQL Server 2000 is the ability to specify an
    alternate snapshot location. This can be done by clicking on the Properties and
    Subscriptions button after selecting the individual publication from Create and
    Manage Publications dialog box (accessible via Tools -> Replication menu).
    In the resulting Publication Properties dialog box, on the Snapshot Location
    tab, you can define a primary (default) or alternative location for snapshot.
    This can be any location accessible to the Snapshot Agent and Subscribers,
    including the FTP folder (login information is defined on the same page).

    Snapshot folder also needs to be accessible by SQL
    Server Agent on
    subscribers when using pull subscriptions. Its account needs to have Read
    permissions to the share and underlying folder (on NTFS level), as well as
    SELECT permissions on a couple of tables in the distribution database (MSrepl_commands
    and MSrepl_transactions).

    In the case of snapshot replication with push
    subscriptions, the SQL Server Account on distributor must have sufficient
    access to the subscribers, in particular CREATE and DROP permissions on the
    target database as well as SELECT, INSERT, EXECUTE, and DELETE on each of the publication

Requirements concerning the Snapshot Agent activities are the same
regardless of replication type. However, when using transactional or merge
replications, you need to keep in mind additional considerations. They relate
to activities of the Distribution and Log Reader Agents specific to
transactional replication, or the Merge Agent in case of merge replication.

The Log Reader Agent operates on the distributor. Its purpose is reading
transaction log entries marked for replication from the publication database
and propagating them to the distribution database. It operates in the security
context of the distributor’s SQL Server Agent. Work involved in applying
changes to subscribers is performed by the Distribution Agent, which, in
addition to access to the distribution database, also needs INSERT, UPDATE, and
DELETE permissions on the target database on subscribers, and read permissions
to the snapshot folder on the distributor.

The Merge Agent maintains synchronization between publisher and subscribers,
in case of merge replication, by monitoring changes on both sides, resolving
possible conflicts, recording outcome and error information in the distribution
database, and applying results to the publisher. This requires SELECT, DELETE,
INSERT and UPDATE permissions on the publisher database. The changes on the
publisher are subsequently applied to subscribers. The exact mechanism used for
updating subscribers depends on whether push or pull subscriptions are used. In
the first case, the Merge Agent running on the distributor, connects to
subscribers and propagates changes to them, which requires INSERT, DELETE and
UPDATE permissions. In the second case, the Merge Agent running on subscribers
connects to the distribution database, retrieves information detailing publisher’s
updates and applies them to subscribers. Note that with pull subscriptions,
there are separate instances of Merge Agents on the distribution and
subscribers. You can take advantage of this fact and configure them with
different accounts, effectively separating the level of access that
subscribers would have to the distribution database (and server). Note that
regardless of its location, the Merge Agent needs Read permissions on the
snapshot folder (by default Program FilesMicrosoft SQL ServerMSSQLRepldata)
on the distributor.

When operating in Active Directory environment, you have the option of
publishing SQL Server instances, their databases and replication articles as
domain objects. On one hand, this makes them easier to locate by legitimate
subscribers; on the other, it exposes them to potentially unauthorized audiences.

As you can see, security aspects of database replication in the SQL Server
2000 are fairly complex. Fortunately, dealing with them is significantly
simplified through the use of wizards, notifications triggered during
configuration, and default settings, which satisfy most common security


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles