Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 296

Designing Transparent Data Encryption
features
Security and User administration always highlights the importance of methods to secure
and control the access to the SQL Server platform from instance level to database scope.
To implement the SQL Server security feature, data encryption at the column level is the
unsurpassed choice to provide security to the data platform. This will enable the first level of
granularity for a specific data, whereas Transparent Data Encryption (TDE) is applicable for the
entire database, most importantly for database and data encryption.
SQL Server 2008 opens up a new dimension in encryption methods by introducing
Transparent Data Encryption (TDE), which allows entire database encryption without affecting
client applications or code changes. This will also encrypt the corresponding database backup
without having any additional step to encrypt such a backup.
In this recipe, let us go through the important aspects of designing transparent
data encryption features using SQL Server 2008 R2. TDE supports several encryption
options, such as AES with 128-bit, 192-bit, 2560-bit, or 3 key triple DES, which are
industry standard methods.
TDE is purely dependent on an encryption key, which is a server asymmetric key that secures
the encrypted database. The Database Encryption Key (DEK) is protected using certificate
storage in a master database of the SQL Server instance where the encrypted user database
is installed.
Getting ready
TDE is an enterprise-wide solution, which requires the Enterprise or Datacenter edition
of SQL Server 2008 R2. For testing purposes, we can use the Developer Evaluation
Enterprise edition.
To design the TDE feature, you are required to complete the following steps:
F Create a master key
F Create or obtain a certificate protected by a master key
F Create a DEK and protect it by the certification
F Set the database to use encryption