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

1/22/2014 12:38:28 AM

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.


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

-- Create database master key (DMK)

-- Create TDE certificate
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
-------------------------------- ----------------------------------

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')

-- Store some unencrypted sensitive data
(CardType varchar(50),
CardNumber varchar(20))

('MasterCard', '1234-1234-1234-1234'),
('American Express', '9876-987653-98765')

-- Create the DEK


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.

  •  SQL Server 2012 : Authentication and Authorization (part 2) - User-Schema Separation,Execution Context
  •  SQL Server 2012 : Authentication and Authorization (part 1) - How Clients Establish a Connection, Password Policies
  •  SQL Server 2012 : SQL Server Security Overview
  •  SQL Server 2012 : Working with Transactions - Transactions in SQL CLR (CLR Integration)
  •  SQL Server 2012 : Distributed Transactions (part 4) - Using a Resource Manager in a Successful Transaction
  •  SQL Server 2012 : Distributed Transactions (part 3) - Distributed Transactions in the .NET Framework - Writing Your Own Resource Manager
  •  SQL Server 2012 : Distributed Transactions (part 2) - Distributed Transactions in the .NET Framework
  •  SQL Server 2012 : Distributed Transactions (part 1) - Distributed Transaction Terminology, Rules and Methods of Enlistment
  •  Protecting SQL Server Data : TRANSPARENT DATA ENCRYPTION (part 6) - Reversing the Implementation of TDE
  •  Protecting SQL Server Data : TRANSPARENT DATA ENCRYPTION (part 5) - Verifying TDE - Verification through Backup and Recovery
    Video tutorials
    - How To Install Windows 8 On VMware Workstation 9

    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Disable Windows 8 Metro UI

    - How To Change Account Picture In Windows 8

    - How To Unlock Administrator Account in Windows 8

    - How To Restart, Log Off And Shutdown Windows 8

    - How To Login To Skype Using A Microsoft Account

    - How To Enable Aero Glass Effect In Windows 8

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen
    programming4us programming4us
    Top 10
    Free Mobile And Desktop Apps For Accessing Restricted Websites
    MASERATI QUATTROPORTE; DIESEL : Lure of Italian limos
    TOYOTA CAMRY 2; 2.5 : Camry now more comely
    KIA SORENTO 2.2CRDi : Fuel-sipping slugger
    How To Setup, Password Protect & Encrypt Wireless Internet Connection
    Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
    Backup & Restore Game Progress From Any Game With SaveGameProgress
    Generate A Facebook Timeline Cover Using A Free App
    New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th