Dela via

  • Artikel

Threat and Vulnerability Mitigation (Replication)

This topic describes techniques to reduce threats to a replication topology.

Encryption

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. 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 2008 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).