In this post I’ll show you how to setup Transparent Data Encryption (TDE). TDE is new in SQL Server 2008 and serves as an encryption method that uses a database encryption key (DEK) to protect SQL Server’s data and log files. The DEK is a key secured by a certificate stored in the master database.
To setup TDE we’ll need to run a few scripts: (My test database is named TDE)
The following script will create the master key with a specified password ElephantRhin0:
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ElephantRhin0'; GO
Next, we’ll create a certificate named TDECert that will be protected by the master key:
USE master; GO CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate'; GO
After creating the certificate we’ll backup the certificate to a specified source:
USE master; GO BACKUP CERTIFICATE TDECert TO FILE = 'C:\TDECert_backup' WITH PRIVATE KEY ( FILE = 'C:\TDECert_key' ,ENCRYPTION BY PASSWORD = 'ElephantRhin0' ) GO
Once the certificate is backed up we will create the DEK using the AES algorithm and protect it by the certificate:
USE TDE; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TDECert; GO
The final step is to set our database to use encryption:
ALTER DATABASE TDE SET ENCRYPTION ON; GO
If everything completed successfully then we have officially encrypted our database with TDE, but don’t take my word for it, run the following query to confirm:
SELECT name, is_encrypted FROM sys.databases WHERE name = 'TDE'