Security for Immediate-updating Subscribers

The Immediate-updating Subscribers option supports either dynamic RPC mode or static RPC mode for the 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 server.

The configuration mode is set automatically when creating push or pull subscriptions:

• 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, the user chooses the desired configuration mode. If you choose static RPC, the server name must already exists. 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™ handles login and password forwarding by adding a replication command to the distribution database to call sp_addsynctriggers at the Subscriber. The login and scrambled password are passed as parameters to the stored procedure. When executed at the Subscriber, sp_addsynctriggers creates immediate-updating triggers and configures the linked server connection.

When executed at the Publisher, the immediate-updating Subscriber stored procedures check the publication access list (PAL) to ensure that the user account executing the remote procedure call (RPC) has permissions to update the data in the publication.

\

See Also

sp_addsynctriggers