Transparent Encryption in SQL Server 2012

TDE is a new feature introduced in SQL Server 2012 that allows an entire DB to be encrypted. Unlike column-level encryption, in TDE the encryption and decryption of data is performed automatically by the DB Engine, and this is fully transparent to the end user and apps. No changes to the DB or apps are needed. Consequently, TDE is the simpler choice when bulk encryption of data is necessary to meet regulatory compliance or corpo-rate data security standards. The encryption of a DB using TDE helps prevent the unauthorized access of data in the scenario in which physical media or backups have been lost or stolen. Transparent data encryption uses a DB encryption key (DEK) for encrypting the DB. The DEK is stored in the DB boot record and is secured by a certificate stored in the master DB. The DB master key is protected by the service master key, which is in turn protected by the Data Protection API. When TDE is enabled on a DB, attaching data files to another SQL Server or the restoring of a backup to another SQL Server is not permitted until the certificate that was used to secure the DEK is available.

As an example, if a hard drive that contains DB files is stolen, without TDE, those DB files can be attached in another SQL Server, thus allowing access to the nonencrypted data in those files. With TDE, the data and log files are automatically encrypted, and the data within these files can’t be accessed without an encryption key. Additionally, backups of DBs that have TDE enabled are also encrypted automatically. We're all familiar with stories about how backup tapes containing sensitive info have been lost or stolen. With TDE, the data in the backup files is completely useless without also having access to the key used to encrypt that data. The encryption and decryption of data with TDE are performed at the page level as data moves between the buffer pool and disk. Data residing in the buffer pools is not encrypted.

TDE's specific purpose is to protect data at rest by encrypting the physical files of the DB, rather than the data itself. These physical files comprise the DB file (.mdf), transaction log file (.ldf), and backup files (.bak). Data pages are encrypted as they are written from the buffer pool to the DB files on disk. Conversely, the data is decrypted at the page level when the data is read in from the files on disk into the buffer pool. The encryption and decryption are done using a background process transparent to the DB user. Although additional CPU resources are necessary to implement TDE, overall, this approach offers much better performance than column-level encryption. According to MS, the performance hit averages only about 3-5%. TDE supports several different encryption options, such as AES with 128-bit, 192-bit, or 256-bit keys or 3 Key Triple DES. You make your choice when implementing TDE.

Deploying Transparent Data Encryption

Like many encryption scenarios, TDE is dependent on an encryption key. The TDE DB encryption key is a symmetric key that secures the encrypted DB. The DEK is protected using a certificate stored in the master DB of the SQL Server where the encrypted DB is installed. Implementing TDE for a specific DB is accomplished by subsequent these steps: