Security and Replication Options

Filtering Published Data

Filtering published data allows you to restrict access to data and allows you to specify the data that is available at the Subscriber. You can filter data horizontally or vertically with any type of replication so partitions based on user requirements and needs can be published to Subscribers.

Additionally, dynamic filters can be used with merge replication and custom data partitions can be created with transactional replication to filter rows based on values retrieved from the Subscriber. For example, using the SUSER_SNAME function in a merge replication dynamic filter, you can propagate just the rows that relate to the value at the Subscriber retrieved by SUSER_SNAME.

For more information, see Filtering Published Data.

Dynamic Snapshots

Dynamic snapshots provide a performance advantage when applying the snapshot of a merge publication with dynamic filters. By using Microsoft® SQL Server™ 2000 bulk copy files to apply data to a specific Subscriber instead of a series of INSERT statements, you will improve the performance when applying the initial snapshot for dynamically filtered merge publications.

The following security considerations must be met to use dynamic snapshots:

• SQL Server on the Publisher must be running under mixed security mode.
  
  
• The login specified as the value of the Publisher login must be in the publication access list (PAL), or be a member of the publication database sysadmin role or db_owner group. This login can be specified in the Create Dynamic Snapshot Job Wizard or by using the -DynamicFilterLogin parameter of the Snapshot Agent.
  
  
• Because SQL Server adds and drops temporary logins in the Snapshot Agent, the Publisher login of the Snapshot Agent must be a member of the securityadmin server role and be a member of the db_owner group on the publication database to generate dynamic snapshots.
  
  
• Dynamic filter logins specified for dynamic snapshot generation must be members of the corresponding PAL.

For more information, see Dynamic Snapshots.

Immediate Updating and Queued Updating

The immediate updating option supports either dynamic remote procedure call (RPC) mode or static RPC mode for the two-phase commit protocol (2PC) connection from the synchronization triggers back to the Publisher.

In dynamic RPC mode, synchronization triggers connect dynamically to the Publisher, using a supplied server name, login, and password. This mode offers increased security for users who do not want a statically defined linked server/remote server connection from a Subscriber to Publisher. It is also easier to use when setting up push subscriptions because the Publisher does not have to be predefined at the Subscriber.

In static RPC mode, synchronization triggers connect to the Publisher over a statically defined server name defined as a linked server or remote server in the sysservers table. This entry is added by an administrator at the Subscriber. The configuration mode is set automatically when creating push or pull subscriptions.

• The immediate updating subscription connection to the Publisher (controlled by sp_link_publication) can use security mode 0 for SQL Server Authentication or 2 for linked server definition to create login mappings. The publication access list (PAL) must include at least one SQL Server Authentication account unless you use security mode 2 and configure delegation (it is possible to set up Windows Authentication in mode 2 by configuring delegation). You can make connections to the Publisher under Windows user accounts invoking the INSERT, UPDATE, and DELETE triggers at the Subscriber using delegation. To set up delegation, see sp_addlinkedsrvlogin.
  
  
• When setting up a push subscription using the Push Subscription Wizard in SQL Server Enterprise Manager or the sp_addsubscription stored procedure, the default configuration uses dynamic RPC at the Subscriber. The dynamic RPC defaults to using the sa login with no password. This is done to avoid sending logins or passwords over the network, and can be changed at the Subscriber using sp_link_publication.
  
  
• When setting up a pull subscription using the Pull Subscription Wizard in SQL Server Enterprise Manager, you choose the desired configuration mode. If you choose static RPC, the server name must already exist. If you choose dynamic RPC, you must supply a login and password that the synchronization triggers will use to connect to the Publisher.
  
  
• When setting up a pull subscription using stored procedures, you must explicitly call sp_link_publication after calling sp_addpullsubscription at the Subscriber.

When using dynamic RPCs, Microsoft® SQL Server™ 2000 handles login and password forwarding by adding a replication command to the distribution database to call sp_addsynctriggers at the Subscriber. When executed at the Subscriber, sp_addsynctriggers creates immediate updating triggers and configures the linked server connection.

When executed, the immediate updating stored procedures at the Subscriber check the PAL at the Publisher to ensure that the user account executing the RPC has permissions to update the data in the publication.