Transparent Data Encryption / TDE in SQL Server: enable, keys & certificates, backups, restore

Transparent Data Encryption (TDE) encrypts SQL Server, Azure SQL Database, and Azure SQL Data Warehouse data files, known as encrypting data at rest. You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.

After reading https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-tde and some blogs, I've developed the following workflow in order to:

• Enable TDE for the database
• Encryption keys & certificates backup
• Restoring database from backup

Steps to perform BEFORE turning TDE on

Before turning TDE on, PLEASE ENSURE there are no connections to the database. The best way is to switch database to Single User mode. Otherwise, you can encounter deadlocks, which will stuck your encryption process and also will prevent your application to connect to the database. Please check the very bottom of this article to find possible solution for such cases.

Create Database Master Key in [master] database

This key will be used to encrypt all others keys in this database.

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123QWEasd';
GO

Create Certificate that will encrypt Database Encryption Key (DEK)

This Certificate will encrypt DEK, that is located in target database and will be used to encrypt database itself.

USE master;  
GO  
CREATE CERTIFICATE MyServerName_SqlTdeMasterKeyCert WITH SUBJECT = 'MyServerName_SqlTdeMasterKeyCert';  
GO

Create Database Encryption Key (DEK) in the database

USE MyDatabaseName;
GO  
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE MyServerName_SqlTdeMasterKeyCert;  
GO  

Enable TDE for the database

USE MyDatabaseName;  
GO  
ALTER DATABASE MyDatabaseName SET ENCRYPTION ON;  
GO

Check the encryption state of the database

SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm
FROM sys.databases db JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id WHERE db.name = 'MyDatabaseName'

Backup Database Master Key & Certificate (with private key)

Use these steps to backup Database Master Key and Certificate in order to restore them later while restoring database from backup on some other server.

USE master;   
GO  
OPEN MASTER KEY DECRYPTION BY PASSWORD = '123QWEasd';   

BACKUP MASTER KEY TO FILE = 'd:\temp\MyServerName_DbMasterKey'   
    ENCRYPTION BY PASSWORD = '123QWEasd2';
GO

USE master; 
GO 
BACKUP CERTIFICATE MyServerName_SqlTdeMasterKeyCert
TO FILE = 'd:\temp\MyServerName_SqlTdeMasterKeyCert.cer'
WITH PRIVATE KEY
(FILE = 'd:\temp\MyServerName_SqlTdeMasterKey.pvk',
ENCRYPTION BY PASSWORD = '123QWEasd3')

Restore Database Master Key and Certificate

 These steps are performed on some other server, where you want to restore your database from backup.

USE master;
GO  
RESTORE MASTER KEY   
    FROM FILE = 'd:\temp\MyServerName_DbMasterKey'   
    DECRYPTION BY PASSWORD = '123QWEasd2' --password to decrypt backup
    ENCRYPTION BY PASSWORD = '123QWEasd'; --password to encrypt restored Master Key in the current database.
GO  

USE master; 
GO 
OPEN MASTER KEY DECRYPTION BY PASSWORD = '123QWEasd';
GO
CREATE CERTIFICATE MyServerName_SqlTdeMasterKeyCert
FROM FILE = 'd:\temp\MyServerName_SqlTdeMasterKeyCert.cer'     
WITH PRIVATE KEY (FILE = 'd:\temp\MyServerName_SqlTdeMasterKey.pvk', 
DECRYPTION BY PASSWORD = '123QWEasd3')

Restore database from backup

USE master
GO 
OPEN MASTER KEY DECRYPTION BY PASSWORD = '123QWEasd';
GO
RESTORE DATABASE MyDatabaseName FROM  DISK = N'd:\temp\MyDatabaseNameBackup.bak' 
WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5;

Troubleshooting TDE

There are number of cases, when TDE encryption can stuck during initial encryption. Possibly - when performing pre-enryption scans. If this is your case, you can try to stop the scan and re-enable encryption:

DBCC TRACEON(5004)
GO
DBCC TRACEOFF(5004)
GO
ALTER DATABASE MyDatabaseName
SET ENCRYPTION ON