With the all new features in SQL Server 2016 always on, which you can read up on here, it’s easy to forget about Transparent Data Encryption (TDE). This blog post will focus on TDE.
TDE encrypts database files at rest. What this means is your .MDF and .NDF Files, and consequently your backups, will be encrypted, meaning you will not be able to detach the database files and restore them on another server unless that server has the same certificate that was used to encrypt the database.
In this blog post I am using SQL Server 2014 and will explain how to enable TDE on an existing AG Group database
- The first thing we need to check is if the server has a master encryption key on all replica in the AG Group
USE MASTER GO SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'
The Screenshot below shows I don’t have a key so I need to create one
- Create a Database Master Encryption Key on each of the replicas in the AG Group. It is important to use a complex password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'C&mpl£[email protected]$$Wrd' GO
- Run the code in step 1 and this time you should see the below
- Now we need to create a certificate to use for the encryption of the database on the primary replica. This can be accomplished by using the below
CREATE CERTIFICATE BackupEncryptionCert WITH SUBJECT = 'SQL Server 2014 AdventureWorks2012 Encryption Certificate'; GO
- Validate the Certificate
SELECT name, pvt_key_encryption_type_desc, thumbprint FROM sys.certificates
The thumbprint will be useful because when a database is encrypted, it will indicate the thumbprint of the certificate used to encrypt the Database Encryption Key. A single certificate can be used to encrypt more than one Database Encryption Key, but there can also be many certificates on a server, so the thumbprint will identify which server certificate is needed
- Next We need to backup the certificate on the Primary Replica
BACKUP CERTIFICATE BackupEncryptionCert TO FILE = 'C:\BackupCertificates\BackupEncryptionCert.bak' WITH PRIVATE KEY ( FILE = 'C:\BackupCertificates\BackupEncryptionCertKey.bak' , ENCRYPTION BY PASSWORD = 'Certi%yC&mpl£[email protected]$$Wrd')
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.
- The Files created in step 6 needs to be copied to each of the other replicas and created in SQL Server. After the files are copied the below command can be used to create the certificates
CREATE CERTIFICATE BackupEncryptionCert FROM FILE = 'C:\BackupCertificates\BackupEncryptionCert.bak' WITH PRIVATE KEY (FILE = 'C:\BackupCertificates\BackupEncryptionCertKey.bak', DECRYPTION BY PASSWORD = 'Certi%yC&mpl£[email protected]$$Wrd');
- That’s all the configuration needed for each instance now we are ready to start encrypting the database. We now need to tell SQL Server which Encryption Type we want to use and which certificate to use. This can be done using the following code on the Primary Replica
Use Adventureworks2012 GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE BackupEncryptionCert
- Finally, the last step is to enable TDE by executing the below command on the Primary Replica
ALTER DATABASE AdventureWorks2012 SET ENCRYPTION ON
And that’s it, I hope you enjoyed this tutorial and found it informative. If you have any questions, please comment below.
I liked seeing how data encryption at rest is implemented differently in SQL Server than it is in MySQL. Thank you for sharing the walkthrough!