Configure TDE encrypted database in SQL Server AlwaysOn Availability Group
TDE has been out there since SQL Server 2008 and it is widely used to protect data/log/backup files at rest. When I talked with DBAs sometimes, even with some very experienced DBAs, I still feel there is some confusing around the terms, such as Service Master Key (SMK), Database Master Key (DMK), Certificate and Database Encryption Key (DEK) etc… especially in the area how we backup, maintain those keys, when we should restore, re-create them. This topic is focusing on how to add a TDE encrypted database to AlwaysOn AG using one of the most commonly used TDE methods: SMK -> DMK -> Certificate -> DEK -> User Database. I added decent amount of comments in the script below, hopefully that can be helpful. 
The following scripts are tested in my lab, feel free to test it in your testing environment.
Lab Environment:
- SQL Server 2016 AlwaysOn AG - 2 nodes:
- Primary - SQL2016N1
- Secondary - SQL2016N2
- Assuming you already had OS Cluster configured and SQL installed. And AlwaysOn AG feature enabled in SQL Server configuration manager.
Basic knowledge:
- SMK - The SMK is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the database master key. The SMK is encrypted by using the local computer key using the Windows Data Protection API (DPAPI). The DPAPI uses a key that is derived from the Windows credentials of the SQL Server service account and the computer's credentials. The service master key can only be decrypted by the service account under which it was created or by a principal that has access to the machine's credentials. SMK is stored in master database.
- DMK - The database master key is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in the database. It can also be used to encrypt data, but it has length limitations that make it less practical for data than using a symmetric key. Noticed here, a DMK can be decrypted by SMK automatically or it can be decrypted by passwords using the OPEN MASTER KEY statement and an known/good password to open it explicitly, I said passwords, it is a plural, meaning a DMK can be decrypted by more than 1 passwords, so, if you don't know its original password when someone created it initially, don't worry, you can simply add a new password to it, as long as the SQL Server instance is running and the DMK can be opened by the SMK. A copy of DMK is stored in master database, and also it stored in the user database that has TDE enabled.
- Certificate - The certificate is encrypted by the DMK, you can consider that a certificate is equivalent to an asymmetric key.
- DEK - The DEK is encrypted by the certificate, and it is the actual key to encrypt the contents of the user database, the encrypted DEK is stored in the boot page of the user database, you can tell the boot page itself is not encrypted, right? Yes, that is correct. The boot page of a user database is not encrypted, the rest of pages are encrypted by the DEK, so, in order to access the rest of pages in the database, the DEK in the boot page must be decrypted first, which requires the certificate.
On the primary:
--create testing database
Create database DB_TDE_Test
--check if the DMK exist in master database
SELECT * FROM
sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'
--if not exist, create one
Use master
Go
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='verycomplicateDMKpassword1234!!!'
--notice the DMK name is ##MS_DatabaseMasterKey## and its keyid is always 101
--if you just created on, this DMK is by default encrypted by the SMK and the password,
--that means you can decrypted it automatically when the SMK present on the server, --or you can decrypted it by the password
SELECT * FROM
sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'
--now you need to create a certificate, which is encrypted by the DMK
CREATE CERTIFICATE TDE_Test_Cert WITH SUBJECT = 'TDE Certificate'
--check if the certificate is created in master database
--A certificate is equivalent to an asymmetric key
select * from sys.certificates
--now it is time to create DEK that is encrypted by the certificate
--DEK is stored in the user database
Use DB_TDE_Test
Create DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Test_Cert
--you will see the warning message:
--Warning: The certificate used for encrypting the database encryption key has not been backed up.
--You should immediately back up the certificate and the private key associated with the certificate.
--If the certificate ever becomes unavailable or if you must restore or attach the database on another server,
--you must have backups of both the certificate and the private key or you will not be able to open the database.
--backup SMK
Use master
Go
--backup SMK and keep it somewhere safe.
--backup SMK is always recommended, just in case when you server is crashed and you might need to restore it on new hardware.
--Restoring SMK is very rare, you don't need to have the same SMK on each nodes.
BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\service_master_key' ENCRYPTION BY PASSWORD = 'SMK_Backup_password123!!!'; --backup DMK
--backup DMK and keep it somewhere safe.
--backup DMK is always recommended, it is similar as SMK, just in case, usually you don't need to restore it.
--Actually based on the DMK creation mechanism, even you restore it, it will be still a different DMK
--But we don't need to worry about that, because DMK is just used to encrypt the certificate,
--as long as the DMK can be opened by the SMK, and the certificate can be decrypted and so the DEK can be decrypted,
--then our database can be accessed eventually. Confusing!!!! still?
Backup master key to file = 'c:\temp\database_master_key' encryption by password = 'DMK_Backup_password123!!!'
--backup certificate (***very important***)
--backup certificate is critical, make sure you always do so, the backup of the certificate will have a pair of files, one is the certificate,
--the other one is the private key file that is encrypted by the given password.
--make sure you keep the password somewhere safe since it will be required when you need to create the same certificate on the other node using the backup file pair.
--if you forgot the certificate's backup password, you can simply back it up again using different password as long as the certificate can be opened by the DMK.
BACKUP CERTIFICATE TDE_Test_Cert TO FILE = 'c:\temp\TDE_Test_Cert'
WITH PRIVATE KEY (
FILE = 'c:\temp\TDE_Test_Cert_Key' ,
ENCRYPTION BY PASSWORD = 'TDE_Cert_Backup_password123!!!'
);
--enable TDE, now the database is TDE encrypted using the
ALTER DATABASE DB_TDE_Test SET ENCRYPTION ON
SELECT * FROM sys.dm_database_encryption_keys;
GO
--check if the database is encrypted/progress (encryption_state = 3 encrypted; =2 in progress)
USE master
GO
SELECT db_name(database_id) [TDE Encrypted DB Name], c.name as CertName, encryptor_thumbprint , dek.* FROM sys.dm_database_encryption_keys dek INNER JOIN sys.certificates c on dek.encryptor_thumbprint = c.thumbprint
On the secondary:
--copy all the backups from the primary node c:\temp\ to the secondary, that way, you don't need to change the file path in the code below
--or, you can modify the code below to reflect the path that matches your testing environment
Use master
Go
--check if DMK exists in master database on the secondary
--if yes, you don't need to create a new one, just use it
SELECT * FROM
sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'
--if the DMK doesn't exist in the master database on secondary, you can either restore the DMK from the backup from the primary, or you can create a new one
--fundamentally, based on the DMK generation mechanism the DMK will be different regardless of your restoring it or creating a new one.
--here I use restore on purpose, just wanted to show you what the each password means below:
--Decryption by password: this is the backup password that you used when you backup the DMK from primary, this will allow you to restore the DMK backup
--Encryption by password: this is a new password that will be used to encrypt/decrypt the DMK once it is restored, this password will allow you to open/decrypt the DMK
--Noticed the above two passwords are for different purpose.
Restore master key from file = 'c:\temp\database_master_key'
Decryption by password ='DMK_Backup_password123!!!' --backup password
Encryption by password = 'verycomplicateDMKpassword1234!!!New' --new DMK encryption password
/* optional
--or create a new DMK
Use master
Go
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='verycomplicateDMKpassword1234!!!New'
*/
--Now, try to open the DMK with the new password, it should open successfully, now, you can alter it to be encrypted by SMK
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'verycomplicateDMKpassword1234!!!New'
--Alter the DMK to be encrypted by the SMK
--The step is usually missed by some customers, what it does is to add the DMK to be encrypted by the SMK
--so that when the SQL Server service starts, the DMK will be automatically decrypted without explicitly opening it.
--this is required for TDE working properly
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
--you can check if the DMK in the master database is now encrypted by the SMK (is_master_key_encrypted_by_server = 1)
select is_master_key_encrypted_by_server, * from sys.databases
where database_id = db_id(N'master')
--perfect! now, let's create certificate on the secondary using the backup pair that we made from the primary node earlier
--***Very important***, once you execute the below statement, the same certificate TDE_Test_Cert will be created from the backup pair,
--noticed that it is now encrypted by the new DMK on the secondary node
CREATE CERTIFICATE TDE_Test_Cert FROM FILE = 'c:\temp\TDE_Test_Cert'
WITH PRIVATE KEY (FILE = 'c:\temp\TDE_Test_Cert_Key' ,
DECRYPTION BY PASSWORD = 'TDE_Cert_Backup_password123!!!'); --the backup password
--check the DMK and the certificate TDE_Test_Cert is created on the secondary
SELECT * FROM
sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'
select * from sys.certificates
On the primary:
--Now it is ready to make a backup of the database [DB_TDE_Test] on the primary (SQL2016N1) and restore it to the secondary (SQL2016N2)
--Go to the primary SQL2016N1
BACKUP DATABASE [DB_TDE_Test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DB_TDE_Test_full.bak'
WITH NOFORMAT, NOINIT, NAME = N'DB_TDE_Test-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [DB_TDE_Test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DB_TDE_Test_tlog.trn'
WITH NOFORMAT, NOINIT, NAME = N'DB_TDE_Test-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
On the secondary:
--Go to the secondary SQL2016N2, restore database with norecovery
--the restore should be successful, since we have all the keys/certificates ready on the secondary
USE [master]
RESTORE DATABASE [DB_TDE_Test]
FROM DISK = N'\\sql2016n1\C$\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DB_TDE_Test_full.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
--apply t-log with norecovery on the secondary
RESTORE LOG [DB_TDE_Test]
FROM DISK = N'\\sql2016n1\C$\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DB_TDE_Test_tlog.trn'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
Now, you can add the database to AG, you cannot use GUI, you have to use t-sql here
On the primary:
--add the database to a AG on the primary (SQL2016N1)
USE master
go
ALTER AVAILABILITY GROUP [AW2016AG] ADD DATABASE [DB_TDE_Test]
On the secondary:
--go to the secondary (SQL2016N2), turn on HADR for the database
USE master
go
ALTER DATABASE [DB_TDE_Test] SET HADR AVAILABILITY GROUP = [AW2016AG];
At this point, the database DB_TDE_Test is added to the AG successfully!
Now, let's clean it up after the test:
----clean up script after the test----
--on the primary replica (SQL2016N1)
use master;
go
ALTER AVAILABILITY GROUP [AW2016AG]
REMOVE DATABASE [DB_TDE_Test];
--drop the test database and its backup history
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DB_TDE_Test'
GO
USE [master]
GO
ALTER DATABASE [DB_TDE_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [DB_TDE_Test]
GO
--drop the certificate
DROP CERTIFICATE TDE_Test_Cert
--drop the DMK
DROP MASTER KEY;
--check if they are cleaned up
select * from sys.certificates;
SELECT * FROM sys.symmetric_keys;
--on the secondary replica (SQL2016N2)
DROP DATABASE [DB_TDE_Test]
--drop the certificate
DROP CERTIFICATE TDE_Test_Cert
--drop the DMK
DROP MASTER KEY;
--check if they are cleaned up
select * from sys.certificates;
SELECT * FROM sys.symmetric_keys;
Thanks for reading! If you have any questions, please feel free to post in the comments area and I will try my best to get back to you asap.