Replication security is an important part of the design and implementation of your distributed application. Replication applies the data changes made elsewhere on the network to the database at your server and vice-versa.
The decentralized availability of replicated data increases the complexity of managing or restricting access to that data. Microsoft® SQL Server™ replication uses a combination of security mechanisms to protect the data and business logic in your application:
By mapping user logins to specific SQL Server roles, SQL Server allows users to perform only those replication and database activities authorized for that role. Replication grants certain permission to the sysadmin fixed server role, the db_owner fixed database role, the current login, and the public role. For example, only members of the sysadmin server role can configure replication.
SQL Server provides a secure administrative link between the Distributor and a remote Publisher. Publishers can be treated as trusted or nontrusted.
The operating system or FTP service prevents users from accessing specific files on the server. The user must have a valid login to read or write the files used in the replication process.
SQL Server allows you to limit access to publications to either registered Subscribers that are well-known to the Publisher, Anonymous, or Subscribers that have logins in the publication access list. SQL Server uses linked server definitions for heterogeneous Subscribers to secure the replication of data with heterogeneous data sources.
By supporting publication access lists (PAL) on each server, SQL Server allows you to determine which logins have access to publications. SQL Server creates the PAL with default logins, but you can add or delete logins from the list.
By supporting agent login security, SQL Server requires each user to supply a valid login account to connect to the server. Replication agents are required to use valid logins when connecting to Publishers, Distributors, and Subscribers. But agents also can use different logins and security modes when connecting to different servers simultaneously.
For immediate-updating Subscribers, SQL Server replication applies security mechanisms to the Publisher-RPC link and Publisher stored procedures.
When used together, these security mechanisms provide the highest safeguards for the data and business logic in your application.
If maximum security is a priority for your application, there are several additional steps you can take to protect your data:
By default, SQL Server version 7.0 configures local Publishers (Publishers located on the same server as the Distributor) as being nontrusted and configures remote Publishers as trusted. Always set the distributor_admin password between the remote Publisher and the Distributor so that all Publishers are treated as nontrusted. To make a SQL Server 6.5 Publisher nontrusted, you must set the password for the sa login at the Publisher to be the same as the password for the distributor_admin login at the Distributor.
Secure the FTP site and the UNC share used for storing snapshot (.sch) files at the Distributor. If you use FTP to download the snapshot files, define the FTP site without Write access. Although this is the default setting for many services, you should confirm that the setting has not been changed after installation.
The Multiprotocol Net-Library supports encryption for user password authentication as well as data. For more information, see Multiprotocol Clients.
SQL Server creates administrative shares for the snapshot folder as a way to help you quickly and easily implement replication. However, you should change the share from an implicit, administrative share to an explicit, nonadministrative share.
Whenever possible, use both a Windows NT user account login and Windows NT Authentication Mode.
Restricting logins to db_owner and including only replicated data in the subscribing database prevents a malicious Publisher from updating data other than what they are publishing.
Using the sysadmin login allows the Publisher to perform operations outside the Subscriber database.
To change the login as a property of the pull subscription:
Whenever possible, include only replicated data in a subscribing database. A malicious Publisher can update only the subscribing database.
SQL Server replication provides two ActiveX controls: SQL Distribution control and SQL Merge control. There are important security considerations that should be thoroughly understood before deploying these controls. For more information, see Scripting Data Access Controls in Internet Explorer.