How to add a TDE encrypted database to an Availability Group

Article
01/07/2015

By default, the Add Database Wizard and New Availability Group Wizard for AlwaysOn Availability Groups do not support databases that are already encrypted: see Encrypted Databases with AlwaysOn Availability Groups (SQL Server).

If you have a database that is already encrypted, it can be added to an existing Availability Group – just not through the wizard. This article provides the steps necessary to successfully add a TDE encrypted database to an AlwaysOn Availability Group.

This scenario has two instances – SQL1 (the AG primary replica instance) and SQL2 (secondary replica instance)

To following pre-requisites are needed:

An existing AlwaysOn Availability Group with at least one Primary and one Secondary replica instance.
A TDE encrypted database on the same instance as the primary replica, online and accessible.
A Database Master Key on all replica servers hosting the availability group (the primary will already have one since it has a TDE encrypted database).

The following actions will be done while adding the TDE encrypted database to the availability group.

Verify each secondary replica instance has a Database Master Key (DMK) in the master DB (create a new one if missing)
On the primary replica instance, create a backup of the certificate used to TDE encrypt the database.
On each secondary replica instance, create the TDE Certificate from the certificate backed up on the primary.
On the primary replica instance, create a full database backup of the TDE encrypted database.
On the primary replica instance, create a transaction log backup of the TDE encrypted database.
On the primary replica instance, add the TDE encrypted database to the Availability Group.
On each secondary replica instance, restore the full backup (with no recovery).
On each secondary replica instance, restore the transaction log backup (with no recovery).
On each secondary replica instance, join the database to the availability group.

Step One: Verify each secondary replica instance has a Database Master Key (DMK) in the master database – if not, create one.

To determine if an instance has a DMK, issue the following query:

USE MASTER GO SELECT * FROM
 sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'

If a record is returned, then a DMK exists and you do not need to create one, but if not, then one will need to be created. To create a DMK, issue the following TSQL on each replica instance that does not have a DMK already:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Mhl(9Iy^4jn8hYx#e9%ThXWo*9k6o@';

Notes – PLEASE READ:

If you query the sys.symmetric_keys without a filter, you will notice there may also exist a “Service Master Key” named: ##MS_ServiceMasterKey## . The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password. For more information regarding the Service Master Key (SMK), please refer to the following article: Service Master Key. We will not need to concern ourselves with the SMK in this article.
If the DMK already exists and you do not know the password, that is okay as long as the service account that runs SQL Server has SA permissions and can open the key when it needs it (default behavior). For more information refer to the reference articles at the end of this blog post.
You do not need to have the exact same database master key on each SQL instance. In other words, you do not need to back up the DMK from the primary and restore it onto the secondary. As long as each secondary has a DMK then that instance is prepared for the server certificate(s).
If your instances do not have DMKs and you are creating them, you do not need to have the same password on each instance. The TSQL command, CREATE MASTER KEY, can be used on each instance independently with a separate password. The same password can be used, but the key itself will still be different due to how our key generation is done.
The DMK itself is not used to encrypt databases – it is used simply to encrypt certificates and other keys in order to keep them protected. Having different DMKs on each instance will not cause any encryption / decryption problems as a result of being different keys.
For more information regarding Transparent Data Encryption (TDE) & Database Master Keys (DMK) see: Transparent Data Encryption (TDE)

Step Two: On the primary replica instance, create a backup of the certificate used to TDE encrypt the database

To decrypt the TDE encrypted database on a secondary replica instance, it will have to have a copy of the certificate on the primary that is used to encrypt the TDE encrypted database. It is possible there is more than one certificate installed on the primary replica instance. To know which certificate to backup, run the following query (on SQL1) and find the certificate name next to the database you wish to add to the availability group:

USE master
GO
SELECT db_name(database_id) [TDE Encrypted DB Name], c.name as CertName, encryptor_thumbprint
FROM sys.dm_database_encryption_keys dek
INNER JOIN sys.certificates c on dek.encryptor_thumbprint = c.thumbprint

It should give a result set similar to the following:

Now backup the certificate using the TSQL command BACKUP CERTIFICATE (on SQL1):

USE MASTER BACKUP CERTIFICATE [TDE_DB_EncryptionCert] TO FILE = 'TDE_DB_EncryptionCert' WITH PRIVATE KEY (FILE = 'TDE_DB_PrivateFile', ENCRYPTION BY PASSWORD = 't2OU4M01&iO0748q*m$4qpZi184WV487')

The BACKUP CERTIFICATE command will create two files. The first file is the server certificate itself. The second file is a “private key” file, protected by a password. Both files and the password will be used to restore the certificate onto other instances.

When backing up the certificate, if no path is provided the certificate and private key files are saved to the default ‘data’ SQL Server database location defined for the instance. For example, on the instance used in this example, the default data path for databases is “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA”.

Note:

If the server certificate has been previously backed up and the password for the private key file is not known, there is no need to panic. Simply create a new backup by issuing the BACKUP CERTIFICATE command and specify a new password. The new password will work with the newly created files (the server certificate file and the private key file).

Step Three: On each secondary replica instance, create the TDE Certificate from the certificate backed up on the primary

Step Two created a backup of the TDE certificate. This step will use that backup to “re-create” or “restore” the certificate on each of the secondary replica instances. The “backup” consists of two files – the server certificate (in this example: “TDE_DB_EncryptionCert”) and the private key file (in this example: “TDE_DB_PrivateFile”) The second file being protected by a password.

These two files along with the password should then be used with the TSQL command CREATE CERTIFICATE to re-create the same server certificate on the other secondary replica instances.

After copying the files to SQL2, connect to a query window on SQL2 and issue the following TSQL command:

CREATE CERTIFICATE [TDE_DB_EncryptionCert] FROM FILE = '<path_where_copied>\TDE_DB_EncryptionCert' WITH PRIVATE KEY ( FILE = '<path_where_copied>\TDE_DB_PrivateFile', DECRYPTION BY PASSWORD = 't2OU4M01&iO0748q*m$4qpZi184WV487')

This installs the server certificate on SQL2.

Repeat as necessary if there are more secondary replica instances. This certificate must exist on all replicas (primary and all secondary replica instances).

Step Four: On the primary replica instance (SQL1), create a full database backup of the TDE encrypted database

TSQL or the SSMS GUI can both be used to create a full backup. Example:

USE master go BACKUP DATABASE TDE_DB TO DISK = 'SOME path\TDEDB_full.bak';

For more information, please review: How to: Create a Full Database Backup (Transact-SQL)

Step Five: On the primary replica instance (SQL1), create a transaction log backup of the TDE encrypted database

TSQL or the SSMS GUI can both be used to create a transaction log backup. Example:

USE master go BACKUP LOG TDE_DB TO DISK = 'SOME path\TDEDB_log.trn';

For more information, please review: How to: Create a Transaction Log Backup (Transact-SQL)

Step Six: On the primary replica instance (SQL1), add the TDE encrypted database to the Availability Group

On the primary (SQL1 in this example) for the availability group (AG_Name), issue an ALTER AVAILABILTY GROUP command:

USE master go ALTER AVAILABILITY GROUP [AG_Name] ADD DATABASE [TDE_DB]

This will add the encrypted database to the primary replica for the availability group called: AG_Name.

Step Seven: On each secondary replica instance, restore the full backup (from Step Four) with no recovery

TSQL or the SSMS GUI can both be used to restore a full backup. Please be sure to specify “NO RECOVERY” so that the transaction log backup can also be restored: Example:

USE master go RESTORE DATABASE TDE_DB FROM DISK = 'SOME path\TDEDB_full.bak' WITH NORECOVERY;

For more information refer to the TSQL RESTORE command.

Repeat step seven for all secondary replica instances if there are more than one,

Step Eight: On each secondary replica instance, restore the transaction log backup (from Step Five) with no recovery

TSQL or the SSMS GUI can both be used to restore a log backup. Please be sure to specify “NO RECOVERY” so that the database remains in a “restoring” state and can be joined to the availability group. Example:

USE master go RESTORE LOG TDE_DB FROM DISK = 'SOME path\TDEDB_log.trn' WITH NORECOVERY;

For more information refer to the TSQL RESTORE command.

Repeat step eight for all secondary replica instances if there are more than one.

Step Nine: On each secondary replica instance, join the database to the availability group

On the secondary (SQL2), join the database to the availability group to begin synchronization by issuing the following TSQL statement:

USE master go ALTER DATABASE TDE_DB SET HADR AVAILABILITY GROUP = [AG_Name];

Repeat step nine on all secondary replica instances

References

Encrypted Databases with AlwaysOn Availability Groups (SQL Server)

 Manually Prepare a Secondary Database for an Availability Group

```
 Transparent Data Encryption (TDE)
```

 Move a TDE Protected Database to Another SQL Server

 SQL Server and Database Encryption Keys (Database Engine)

 DMV - sys.dm_database_encryption_keys (Transact-SQL)

 DMV - sys.symmetric_keys (Transact-SQL)

 DMV – sys.certificates (Transact-SQL)

```
 Service Master Key
```

 SQL Server Certificates and Asymmetric Keys

Comments

Anonymous
March 31, 2017
The article above was great - only 1 omission - at step 3 you need to open the master key before trying to create the certificate on the secondary.
Anonymous
July 13, 2017
I have a server where certificate is created in a user database A rather than on master DB. Now as per step 2 I have taken backup of certificate in user database A. Now my question is how can I create certificate on secondary database B without restoring it on secondary replica?I don't want to use master DB certificate.
Anonymous
July 20, 2017
USE MASTER GO SELECT * FROM I think the 'GO' is unecessary

Share via