Elements of Replication Security

  • Article

Three areas to consider when securing any software system are authentication, authorization and encryption:

  • Authentication is the process by which an entity (typically a computer in this context) verifies that another entity, also called a principal, (typically another computer or user) is who or what it claims to be.
  • Authorization is the process by which an authenticated principal is given access to resources, such as a file in the file system, or a table in a database.
  • Encryption is the process of converting data into a form that cannot be read without a special key, so that only the intended recipient can read the data.

Authentication and Authorization

Replication security uses authentication and authorization to control access to replicated database objects and to the computers and agents involved in replication processing. This is accomplished through three mechanisms:

  • Agent security
    The replication agent security model allows fine-grained control over the accounts under which replication agents run and make connections. For detailed information about the agent security model, see Replication Agent Security Model. For information about setting logins and passwords for agents, see Managing Logins and Passwords in Replication.
  • Administration roles
    Ensure that the correct server and database roles are used for replication setup, maintenance, and processing. For more information, see Security Role Requirements for Replication.
  • The publication access list (PAL)
    Grant access to publications through the PAL. The PAL functions similarly to a Microsoft Windows access control list. When a Subscriber connects to the Publisher or Distributor and requests access to a publication, the authentication information passed by the agent is checked against the PAL. For more information and best practices for the PAL, see Securing the Publisher.

Encryption

Replication does not encrypt data stored in tables or sent over network connections. This is by design, because encryption is available at the transport level with a number of technologies, including the following industry standard technologies: Virtual Private Networks (VPN), Secure Sockets Layer (SSL), and IP Security (IPSEC). We recommend using one of these encryption methods for the connections between computers in a replication topology. For more information, see Encrypting Connections to SQL Server. For information about using VPN and SSL for replicating data over the Internet, see Securing Replication Over the Internet.

If you use SSL to secure the connections between computers in a replication topology, specify a value of 1 or 2 for the -EncryptionLevel parameter of each replication agent (a value of 2 is recommended). A value of 1 specifies that encryption is used, but the agent does not verify that the SSL server certificate is signed by a trusted issuer; a value of 2 specifies that the certificate is verified. Agent parameters can be specified in agent profiles and on the command line. For more information, see:

Replication has the following behavior with respect to database Master Keys, which are used to encrypt data:

  • If a Master Key is present in a database involved in replication (a publication database, subscription database, or distribution database), replication encrypts and decrypts agent passwords in that database using a SQL Server 2005 database symmetric key. If Master Keys are used, a Master Key should be created in each database involved in replication. If a Master Key is not present in a database involved in replication, replication encrypts and decrypts agent passwords in that database using the algorithm available in SQL Server 2000. For more information about creating Master Keys, see CREATE MASTER KEY (Transact-SQL).
  • Replication does not replicate Master Keys. If you require the Master Key at the Subscriber, you must export it from the publication database using BACKUP MASTER KEY and then import it into the subscription database using RESTORE MASTER KEY. For more information, see BACKUP MASTER KEY (Transact-SQL) and RESTORE MASTER KEY (Transact-SQL).
  • If a Master Key is defined for an attachable subscription database, specify the Master Key password using the @db_master_key_password parameter of sp_attachsubscription (Transact-SQL). This allows the database to be attached at the Subscriber.

For more information about encryption and Master Keys, see Encryption Hierarchy.

Replication enables you to publish encrypted column data. To decrypt and use this data at the Subscriber, the key that was used to encrypt the data at the Publisher must also be present on the Subscriber. Replication does not provide a secure mechanism to transport encryption keys. You must manually re-create the encryption key at the Subscriber. For more information, see How to: Replicate Data in Encrypted Columns (SQL Server Management Studio).

Filtering Published Data

In addition to using authentication and authorization to control access to replicated data and objects, replication includes two options to control what data is available at a Subscriber: column filtering and row filtering. For more information about filtering, see Filtering Published Data.

When you define an article, you can publish only those columns that are necessary for the publication, and omit those that are unnecessary or contain sensitive data. For example, when publishing the Customer table from the AdventureWorks database to sales representatives in the field, you can omit the AnnualSales column, which might be relevant only to executives at the company.

Filtering published data allows you to restrict access to data and allows you to specify the data that is available at the Subscriber. For example, you can filter the Customer table so that corporate partners only receive information about those customers whose ShareInfo column has a value of "yes." For merge replication, there are security considerations if you use a parameterized filter that includes HOST_NAME(). For more information, see the section "Filtering with HOST_NAME()" in Parameterized Row Filters.

See Also

Concepts

Replication Security Best Practices

Other Resources

Security Considerations for Replication

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
New content: