After my article about Automatic Database Seeding  had been published, I got a question about how that can work with a TDE database. Coincidentally at the same time at work, I had been assigned a project that related to TDE. So, I have decided to share my knowledge about TDE on Availability Groups and do some practice work at the same time.

Basic Operations with Certificates

Understanding of some basic operations with certificates are crucial for being able to manage Transparent Data Encryption on an Availability Group. I suppose that the reader is familiar with Transparent Data Encryption on a standalone server, but I want to emphasize importance of some operations. Also, I will use scripts from this section multiple times in this article, and I will refer a reader to this section in order to avoid repeating myself.

The most important from my point of view is have a backup of a certificate handy, to make a backup you can use the following script (script 1):

USE [master];
GO
BACKUP CERTIFICATE MyNewTDECert
TO FILE = '\\shared_folder\my_secrets\MyNewTDECert.cer'
WITH PRIVATE KEY (FILE = '\\shared_folder\my_secrets\MyNewTDECert.key',
ENCRYPTION BY PASSWORD = 'Super$ecretPassW0rd111');
GO

This scrip takes a backup of the certificate ‘MyNewTDECert’ including the private key in two files on the shared location, ‘\\shared_folder\my_secrets\’, with the private key encrypted with a password. You need to change a certificate name, the path, and the password to the values which are relevant to your environment.

Please make sure that you keep these files in secure location and the password in an encrypted form, so you will be able to restore that certificate when that is required, and it won’t be easy to steal by a 3rd party. More information about this command can be found on the official Microsoft BOL page.

The second important task is certificate restore, which can be performed using the following script (script 2):

USE [master];
GO
CREATE CERTIFICATE MyNewTDECert
FROM FILE = '\\shared_folder\my_secrets\MyNewTDECert.cer'
WITH PRIVATE KEY (FILE = '\\shared_folder\my_secrets\MyNewTDECert.key',
DECRYPTION BY PASSWORD = 'Super$ecretPassW0rd111');
GO

Again, you need to replace the paths, the certificate name, and the password with values that are relevant to your infrastructure. When you restore an expired certificate, you will see a warning message, but TDE will work fine. It is recommended that you renew all expired certificates, as will be shown later. Additional information about this command can be found on the official BOL page.

Note: please make sure that you have all the TDE certificates backed up before making any changes.

The code to find out with which certificate my database has been encrypted with is shown in the following script. This needs to be executed against the database with TDE enabled (script 3):

select create_date, regenerate_date, modify_date, set_date, opened_date, 
key_algorithm, key_length, encryptor_type, encryptor_thumbprint
from sys.dm_database_encryption_keys
where database_id = db_id();

The script will return the ‘encryptor_thumbprint’ of the certificate that was used to encrypt the Database Encryption Key (DEK). This also shows other useful information: the encryption algorithm and key length. To get the actual certificate information from the ‘encryptor_thumbprint’ you can use the following T-SQL script (script 4):

select name, subject, start_date, expiry_date
from master.sys.certificates
where thumbprint = 0x7777777777777777777;

Note: you need to replace ‘0x7777777777777777777’ with the actual ‘encryptor_thumbprint’.

The code to create a new certificate the with the custom expiration date is shown in the script below (script 5):

USE [master];
GO
CREATE CERTIFICATE MyNewTDECert
WITH SUBJECT = 'MyNewTDECert’,
EXPIRY_DATE = '20200131';
GO

Note: This is the same command that is used to restore a certificate.

Replace an Expired TDE Certificate

To replace the TDE certificate, you need to create a new one. I would recommend using the script that creates a new certificate with a custom expiration data from the first section (script 5). The following script can be used to replace the encryption certificate for your database, in this case the database is ‘MySecretDatabase’ and the certificate is ‘MyNewTDECert’:

USE [MySecretDatabase];
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE MyNewTDECert;
GO

You may see a warning that the certificate hasn’t been backed up. Please make sure you take backups of all certificates you use for TDE. The backup script has been provided in the first chapter (script 1).

When the new TDE certificate has been assigned please check that the TDE database use the new certificate. That can be achieved using the scrips from the first chapter (scripts 3 and 4). After that has been done you can drop the old certificate using the following script:

USE [master];
GO
DROP CERTIFICATE ExpiredTDECert;
GO

That is all what is required in order to renew a TDE certificate for a database which is not in an Availability Group.

Note: make sure that you keep a backup of an old certificate in case you need to restore a database to the point in time before we switched the encryption certificate.

Replace an Expired TDE certificate on a Database in an AG

Before making any changes, please check that the AG is healthy, and the database is synchronized across the replicas. For that purpose I usually use the Availability Groups Dashboard from Microsoft SQL Server Management Studio:

For an Availability Group, you should create the new certificate on the primary node (script 5), then create a backup with the private key (script 1). After that, you must distribute the certificate with the private key among all the AG nodes. You may use the import script from the first section (script 2). You can also use the script from the first section (script 4) in order to make sure that all replicas have the certificate with the same thumbprint. When that has been done can you alter the database encryption key on the primary replica and drop the old certificate from all the replicas.

The database encryption key (DEK) can be altered with a new certificate using the script below:

USE [MySecretDatabase];
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE MyNewTDECert;
GO

We can remove an old expired certificate the same way it was done for a TDE database outside of an AG from all participating replicas:

USE [master];
GO
DROP CERTIFICATE ExpiredTDECert;
GO

Please check that you Availability Group is healthy and the TDE in synchronized state after you have renewed the TDE certificate. Keep the new certificate in a secure and reliable storage so you would be able to restore your TDE database in case of disaster or migration.

Note: the process of renewing certificate is very similar to the initial activation of the TDE for a database. Also, before doing any manipulations on the AG, make sure that the AG is healthy, and databases are synchronised.

Note: make sure that you keep a backup of an old certificate in case you will need a restore database to the point in time before we switched the encryption certificate.

Automatic Database Seeding with a TDE database

If you have read my article about this feature you can find some useful information about TDE and Automatic Database Seeding in the article discussion topic. If you haven’t read it, please do so.

If you want to use TDE databases with automatic database seeding, you must distribute encryption certificates among all the participating replicas before you add a database into an AG. Here is the simple step by step instruction:

1. You must have TDE enabled on the primary replica in order to continue with this process.
2. Backup the TDE certificate from the primary replica (script 1)
3. Distribute the TDE certificate among all participating replicas (script 2)
4. Optionally you can check that you have the correct certificate distributed among all participating replicas (script 4)
5. Then you can follow instructions from the section ‘Adding a Database with Automatic Seeding’ in the article. Remember in the case of a TDE database, you cannot use GUI option, so you must use the T-SQL script options for SQL Server 2016. That may change in the future versions of SQL Server.

Additional information about TDE can be found on the official Microsoft web site.

Conclusion

Sometimes it is difficult to combine some SQL Server features because of many limitations. But as you can see it is possible to combine TDE, Availability Groups, and Automatic Database Seeding for a database running on a SQL Server 2016 instance. The only one limitation I found during testing of such configurations is that you won’t be able to use graphic interface to add database in an AG. I hope that article will help you with your work.