Replication

Setting up replication

You may encrypt an already replicated database. At the moment of encryption, all current connections from replication agents to the database will be terminated. Until you setup passwords, it will report various replication errors.

If you setup replication for an already encrypted database make sure that SSMS has access to encrypted databases. To create a new publication, you must have access to a Publisher database. To setup Subscription, you must have access to Subscriber and Publisher databases, otherwise, it will fail to create a subscription or replication.

You can unlock access by adding SSMS to the Client Program Configuration and setting passwords for databases. Or you may unlock access with SQL commands on the Subscriber's and Publisher's SQL Server:

use yourdatabase
go
open symmetric key dbdx decryption by password='mypass'
go
dbd_unlock_for_host

Setting up passwords for replication agents

Update: There is an alternative method for accessing an encrypted database. You may allow access to an encrypted database for a certain SQL Server logins automatically.

Read more

Replication configuration was changed in version 4. If the Publisher's or Subscriber's database is encrypted you need to supply passwords for replication agents. Those passwords are securely stored and can't be copied. This applies only to replication agents.

Replication4 650x446

As you know, there are 3 logical participants in replication: Publisher, Distributor and Subscriber. They can all be on one computer and even on one SQL instance or they can be on different computers. Using Configurator, you need to setup passwords for all encrypted databases.

Rules:

If the Publisher database is encrypted, its database/password must be setup on a Distributor computer. In the case of a Merge Pull Replication, the Subscriber's server must have a password for the Publisher's database.

If the Subscriber's database is encrypted:

  • Push replication: its password must be set up on a Distributor computer.
  • Pull replication: its password must be set up on a Subscriber computer.

If Distributor is a separate SQL Server without any encrypted databases, you do not need to install the DbDefence server module on that SQL Server. You need to install the "Only Replication" part from the installer. In this case, it will skip SQL Server selection and install Configurator and the files required to run replication agents.

After you enter the passwords do not forget to click Save. Saved passwords may not be instantly applied to agents. That happens because replication agents are still trying to access protected databases without passwords and have not yet restarted. They will restart after several minutes, then login to the database with the password. To immediately force replication agents to use passwords, restart the SQL Server Agent on that computer.