TDE is encryption of the database, by using which we can encrypt the database at rest. The ‘rest’ here means that the data files, log files and the backup files are encrypted at the storage level. When SQL Server requires to read this data it gets decrypted and stored in the memory and so once in memory any next operation will be on plain text data only.
No statement can better explain the TDE implementation than the above pic let’s see how it is done.
Create a master key.
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
Create a server certificate, which is basically a certificate created in the master database.
USE master; GO CREATE CERTIFICATE MyTdeCert WITH SUBJECT = 'My DEK Certificate';
Create database encryption key using the server certificate created above.
USE DatabaseToEncrypt; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyTdeCert; GO
Enable encryption for your database, it will use the Database Encryption Key created above to encrypt your data at rest.
USE DatabaseToEncrypt; GO ALTER DATABASE DatabaseToEncrypt SET ENCRYPTION ON; GO
Backup the Server Certificate
Now once you have got your database encrypted the important thing to do is to take the backup of your server certificate.
BACKUP CERTIFICATE MyTdeCert TO FILE = 'S:\Somefolder\TDECertificate.cer' WITH PRIVATE KEY( FILE = 'S:\Somefolder\TDECertificate.key', ENCRYPTION BY PASSWORD = 'P@$$W0rD’ );
Restore the Server certificate
If you want to use this TDE encrypted database anywhere you should know how to restore this server certificate.
CREATE CERTIFICATE MyTdeCert FROM FILE = 'S:\Somefolder\TDECertificate.cer' WITH PRIVATE KEY( FILE = 'S:\Somefolder\TDECertificate.key’, DECRYPTION BY PASSWORD = 'P@$$W0rD’ -- It is same password used while taking backup );
When restoring this certificate on another server make sure that SQL Server service account has full control on the folder in which you have stored your certificate and private key, and also on the certificate and private key itself.
If you know how to restore your server certificate than you are done with managing this TDE enabled database.
- If you need to configure Always-On, move the certificate and restore it at replica servers. Once done take full and log backup and restore at all the replicas manually (obviously with
- When configuring Log shipping to a TDE database, restore this server certificate at secondary end and you are good to go with configuring it.
- When configuring Replication restore the server certificate at subscriber before configuring it.
- When restoring a backup of this database we need to have the certificate restored at the destination.
Somethings to keep in mind when implementing TDE:
- When any database is encrypted in an instance then Tempdb is also TDE enabled.
- Doesn’t encrypt data in buffer pool.
- It is an Enterprise only feature.
- Enabling TDE is not instantaneous and can cause blocking.
- To pause TDE encryption use Trace flag 5004, once required to resume re run
ALTER DATABASE DatabaseToEncrypt SET ENCRYPTION ON.
To disable TDE use below commands:
USE MASTER; GO ALTER DATABASE DatabaseToDecrypt SET ENCRYPTION OFF; GO