SQL Server 2000 Security - Part 9 - Replication Security

August 10, 2004

The previous article 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.

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 Files\Microsoft SQL Server\MSSQL\Repldata 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_server\C$\Program Files\Microsoft SQL Server\MSSQL\Repldata, 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 distributor).

  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 articles.

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 Files\Microsoft SQL Server\MSSQL\Repldata) 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 requirements.

» See All Articles by Columnist Marcin Policht