Replication Security (Transact-SQL)

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™ 2000 replication uses a combination of security mechanisms to protect the data and business logic in your application:

• Role requirements

  By mapping user logins to specific SQL Server 2000 roles, SQL Server 2000 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.

• Distributor administrative link security

  SQL Server 2000 provides a secure administrative link between the Distributor and a remote Publisher. Publishers can be treated as trusted or nontrusted.

• Snapshot folder security

  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.

• Registered subscribers

  SQL Server 2000 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 2000 uses linked server definitions for heterogeneous Subscribers to secure the replication of data with heterogeneous data sources.

• Publication access lists

  By supporting publication access lists (PAL) on each server, SQL Server 2000 allows you to determine which logins have access to publications. SQL Server 2000 creates the PAL with default logins, but you can add or delete logins from the list.

• Agent login security

  By supporting agent login security, SQL Server 2000 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. However, agents also can use different logins and security modes when connecting to different servers simultaneously.

• Immediate-updating Subscriber security

  For immediate-updating Subscribers, SQL Server 2000 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.