SQL Server 2012 : Encryption Support (part 3) - Transparent Data Encryption - Creating Keys and Certificates for TDE

Transparent Data Encryption

SQL Server can encrypt the entire database (both the data and the log files) automatically, and without requiring any programming or application changes on your part. This is achieved using a special feature in SQL Server, available only in the Enterprise edition, called Transparent Data Encryption (TDE). Data is encrypted on the fly as it is written to disk and decrypted when it is read back. Encryption is performed at the page level and does not increase the size of the database. Because the process is entirely transparent, it’s extremely easy to use TDE in SQL Server. Let’s see how.

Note

The NTFS file system in Windows Server 2000 and later provides a feature called Encrypted File System (EFS). This feature also applies transparent encryption to any data stored on the hard drive, but it will not protect databases or backups that have been copied onto a CD or other media. TDE in SQL Server 2008 is based on a certificate that is needed to decrypt or restore any encrypted database, regardless of where the data is transferred.

Creating Keys and Certificates for TDE

To start using TDE, you need to create a certificate, and to create a TDE certificate, you need to create a DMK. Although DMKs and certificates, in general, can be created in any database, they must be created in the master database when you intend to use them for TDE. The code in Example 2 creates a DMK and certificate in the master that can be used for TDE against any database and then queries the sys.certificates view to confirm that the certificate exists.

Example 2. Creating a database master key and certificate for transparent data encryption.

-- Create a DMK and Certificate in master
USE master
GO

-- Create database master key (DMK)
CREATE MASTER KEY
 ENCRYPTION BY PASSWORD = 'Hrd2GessP@ssw0rd!'

-- Create TDE certificate
CREATE CERTIFICATE MyEncryptionCert
 WITH SUBJECT = 'My Encryption Certificate'

-- Verify that the certificate has been created and is protected by the DMK
SELECT name, pvt_key_encryption_type_desc FROM sys.certificates
 WHERE name = 'MyEncryptionCert'

The output verifies that the certificate was created and that its private key is protected by the DMK, as shown here:

name                             pvt_key_encryption_type_desc
-------------------------------- ----------------------------------
MyEncryptionCert                 ENCRYPTED_BY_MASTER_KEY

Next, create a database with sensitive information in it, and then create a database encryption key (DEK), as shown in Example 3.

Example 3. Creating a database encryption key.

-- Create a new database
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MyDB')
 DROP DATABASE MyDB
GO
CREATE DATABASE MyDB
GO
USE MyDB
GO

-- Store some unencrypted sensitive data
CREATE TABLE CreditCardInfo
(CardType varchar(50),
 CardNumber varchar(20))
GO

INSERT INTO CreditCardInfo VALUES
 ('MasterCard', '1234-1234-1234-1234'),
 ('American Express', '9876-987653-98765')

-- Create the DEK
CREATE DATABASE ENCRYPTION KEY
 WITH ALGORITHM = AES_128
 ENCRYPTION BY SERVER CERTIFICATE MyEncryptionCert

Note

Because you have not yet backed up the server certificate, SQL Server issues a warning at this time alerting you to this fact. This warning should be taken seriously, since you will not be able to access any database encrypted by the DEK without the certificate. Should the certificate be lost or damaged, your encrypted databases will be completely inaccessible. Later in this section, you will learn how to back up and restore the certificate.

This statement creates a DEK for the MyDB database. Based on this DEK, SQL Server will encrypt MyDB using the AES_128 algorithm. The WITH ALGORITHM clause can also specify AES_192, AES_256, or TRIPLE_DES_3KEY to be used for the encryption algorithm. The DEK itself is encrypted using the MyEncryptionCert certificate you created in the master database. (A DEK can be encrypted only by a certificate; it cannot be encrypted with just a password.) The DEK will be used not only to encrypt the database, but also to protect the database backups. Without the certificate used to encrypt the DEK, an encrypted database backup cannot be restored anywhere—end of story.