TDE and Availability Groups

Caution must be exercised when mixing Transparent Data Encryption (TDE) and Availability Groups to ensure databases don't end up in a suspect state on the secondary node.

Adding TDE to a database in an Availability Group

If a database is already part of an Availability Group, the certificate used to encrypt the database needs to be created on both nodes with the same thumbprint before the database encryption is turned on.

Create master key in master database on both nodes

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Bollo_20140724';

Create certificate on primary node

USE master

GO

CREATE CERTIFICATE TDECertificate

WITH SUBJECT = 'SQL Server TDE Certificate';

Take a backup of certificate and copy to secondary node

BACKUP CERTIFICATE TDECertificate

TO FILE = 'C:\TDECertificate.cer'

WITH PRIVATE KEY

(FILE = 'C:\TDECertificate.PVK',

ENCRYPTION BY PASSWORD = 'Higuma_20140724')

Restore on secondary node and confirm the thumbprints are the same

CREATE CERTIFICATE TDECertificate

FROM FILE = 'C:\TDECertificate.cer'

WITH PRIVATE KEY (FILE = 'C:\TDECertificate.PVK',

DECRYPTION BY PASSWORD = 'Higuma_20140724')

Create database encryption key and start encryption

USE Test

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDECertificate;

USE test

ALTER DATABASE test

SET ENCRYPTION ON

Adding a TDEed database to an Availability Group

If a TDE enabled database is yet to be added to an Availability Group,

the certificate used to protect the database encryption key needs to be created on the secondary node  with the same thumbprint before the database is added to the Availability Group. The steps are very similar to previous point.

Take a backup of certificate from the instance with the TDE enabled database.

/*on Primary Instance*/

BACKUP CERTIFICATE TDECertificate

TO FILE = 'C:\TDECertificate.cer'

WITH PRIVATE KEY

(FILE = 'C:\TDECertificate.PVK',

ENCRYPTION BY PASSWORD = 'Higuma_20140724')

Copy to the secondary node and restore.

/*on Secondary Instance*/

CREATE CERTIFICATE TDECertificate

FROM FILE = 'C:\TDECertificate.cer'

WITH PRIVATE KEY (FILE = 'C:\TDECertificate.PVK',

DECRYPTION BY PASSWORD = 'Higuma_20140724')

Using the database wizard to add the database to the availability group will give an error, stating the database contains an encryption key. As the error message says, use TSQL commands to add the database to the availability group instead.

/*On principal instance*/
USE Master
GO
ALTER AVAILABILITY GROUP <AVGroup> ADD DATABASE test;

/*On secondary instance*/
USE Master
GO
ALTER DATABASE test SET HADR AVAILABILITY GROUP = <AVGroup>;

The encrypted database will now be part of the Availability Group.

The INCORRECT way

Its important to make sure the certificate used to protect the database encryption key is restored onto the secondary node before turning on encryption for a database in an Availability Group. Failing to do will cause the database to turn suspect on the secondary node.

The following steps demonstrate the how to create a suspect database on the secondary node. The TSQL commands from previous steps can be used.

• Add non-TDE enabled database to Availability Group
• Create the master key on the Primary Instance
• Create the certificate on the Primary Instance
• Create the database encryption key in the database added to the Availability Group
• Enable TDE in the database on the Primary Instance

Querying sys.databases on the Primary Instance returns....

the same query on the Secondary Instance returns...

Trying the drop database will give the following error...

Its possible to remove the database from the Availability Group, which changes the database state to restoring but still not possible to drop the database. The same error is returned. Its also possible to restore the certificate from the Primary Instance but this doesn't allow the database to be dropped.

Unfortunately, the only way to remove the restoring\suspect database from the secondary instance is to stop the SQL service, (re)move the database and transaction log files and restart the SQL service.