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