Securing and protecting SQL Server data, log and backup files with TDE

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'

SQL Freelancer SQL Server Transparent Data EncryptionClick here to view the rest of this post.

Leave a Comment.