How to Enable Transparent Data Encryption

Security of data is a hot topic these days.  One of the security concerns auditors have is whether or not your SQL Server data is encrypted when stored At-Rest–meaning auditors would like to see all data encrypted on the actual disk platters that store the data.   By default, SQL Server does not encrypt data in a SQL Server database in an encrypted format.  When SQL Server 2008 was introduced, Microsoft implemented Transparent Data Encryption (TDE).  When TDE is enabled on a database SQL Server will encrypt the database as data is written to the disk.  Additionally, when data is read from disk it will be unencrypted.  When data is in memory is it is in an unencrypted format.  

By implementing TDE, DBAs are able to meet auditor’s encryption requirements. To enable a database to use TDE you can use the following steps:

Step 1: Create Database Master Key

USE master;
GO
CREATE MASTER KEY ENCRYPTION
       BY PASSWORD='Provide Strong Password Here For Database Master Key';
GO

Step 2: Create a Certificate to support TDE

USE master;
GO 
CREATE CERTIFICATE TDE_Certificate
       WITH SUBJECT='Certificate for TDE';
GO

Step 3: Create Database Encryption Key

USE MyData
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

Step 4: Enable TDE on Database

ALTER DATABASE MyData SET ENCRYPTION ON;
GO

Step 5: Backup the Certificate

This step is not required to encrypt a database using TDE.  But to make sure you can recover your encrypted data from a database backup, should your instance database become corrupted, or you want to move an encrypted database to another server, you should backup the certificate.  To accomplish that backup run the following code:

USE master;
GO
BACKUP CERTIFICATE TDE_CERT_For_MyData
TO FILE = 'C:tempTDE_Cert_For_MyData.cer'
WITH PRIVATE KEY (file='C:tempTDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='Provide Strong Password for Backup Here');

Note;  Remember to store the PASSWORD you use here in a safe place.  You will need this password to restore  the certificate if you need to move your database to another server, or have to rebuild the instance that hosts your encrypted database.

One of the side benefits of turning on TDE for a database is the database backups will contain encrypted data.   By having an encrypted backup, you further secure your SQL Server data.  When a database backup is encrypted you can only restore it to a server that contains the same certificate that encrypted the data.  Therefore, in order to restore a TDE encrypted backup to another server you will first need to move the certificate used to encrypt the database to the server where the database backup is being restored.    See Tip “Moving TDE Encryption Key To Another Server”  to find out more about moving your encryption key.

See all articles by Greg Larsen