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;
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
- Hits: 7723