Security Considerations for Updating Subscriptions
In addition to the general considerations for subscriptions, there are a number of considerations for updating subscriptions.
Immediate Updating Subscriptions
When you configure an immediate updating subscription, you specify an account at the Subscriber under which connections to the Publisher are made. Connections are used by the triggers that fire at the Subscriber and propagate changes to the Publisher. There are three options available for the type of connection:
- A linked server that replication creates; the connection is made with the credentials you specify at configuration time.
- A linked server that replication creates; the connection is made with the credentials of the user making the change at the Subscriber.
- A linked server or remote server that you have already defined.
Important
To specify connection information, use the stored procedure sp_link_publication (Transact-SQL). You can also use the Login for Updatable Subscriptions page of the New Subscription Wizard, which calls sp_link_publication. Under certain conditions, this stored procedure can fail if the Subscriber is running SQL Server 2005 Service Pack 1 (SP1) or later, and the Publisher is running an earlier version. If the stored procedure fails in this scenario, upgrade the Publisher to SQL Server 2005 SP1 or later.
For more information, see:
- SQL Server Management Studio: How to: Create an Updatable Subscription to a Transactional Publication (SQL Server Management Studio) and How to: View and Modify Replication Security Settings (SQL Server Management Studio)
- Replication Transact-SQL programming: How to: Create an Updatable Subscription to a Transactional Publication (Replication Transact-SQL Programming) and How to: View and Modify Replication Security Settings (Replication Transact-SQL Programming)
Important
The account specified for the connection should only be granted permission to insert, update, and delete data on the views that replication creates in the publication database; it should not be given any additional permissions. Grant permissions on views in the publication database that are named in the form syncobj_<HexadecimalNumber> to the account you configured at each Subscriber.
Queued Updating Subscriptions
When you configure queued updating subscriptions, there are two areas to keep in mind that relate to security:
There is only one Queue Reader Agent for each Distributor. It is recommended that for each Distributor, you configure at most one publication that is enabled for queued updating subscriptions.
The Queue Reader agent makes connections to the Distributor, Publisher, and each Subscriber:
- The account under which the agent runs and makes connections to the Distributor is specified when you create the agent (if you use the New Publication Wizard, the agent is created when you create a publication that is enabled for updating subscriptions).
- The account under which the agent makes connections to the Publisher is specified when you configure distribution for a Publisher. Specify the Windows account under which the agent runs or a SQL Server account.
- The account under which the agent makes connections to the Subscriber is specified when you create the subscription.
Important
Use SQL Server Authentication for connections to Subscribers, and specify a different account for the connection to each Subscriber. If you use a pull subscription, replication always sets the connection to use Windows Authentication (for pull subscriptions, replication cannot access metadata at the Subscriber required to use SQL Server Authentication). In this case, change the connection to use SQL Server Authentication after the subscription is configured.
For more information, see:
- SQL Server Management Studio: How to: Create an Updatable Subscription to a Transactional Publication (SQL Server Management Studio) and How to: View and Modify Replication Security Settings (SQL Server Management Studio)
- Replication Transact-SQL programming: How to: Create an Updatable Subscription to a Transactional Publication (Replication Transact-SQL Programming) and How to: View and Modify Replication Security Settings (Replication Transact-SQL Programming)
See Also
Concepts
Replication Security Best Practices
Securing Subscribers
Other Resources
Security Considerations for Replication