Protecting SQL Server Data : TRANSPARENT DATA ENCRYPTION (part 3) - Implementing TDE - The User Database

4.3 The User Database

Having created the database master key and the MasterCert certificate in the Master database, we are ready to create the database encryption key for the HomeLending database which we will use to perform the cryptographic functions for the physical files of our database.

The database encryption key is created using the CREATE DATABASE ENCRYPTIONKEY command. The arguments to this method include:

• WITH ALGORITHM: Specifies the algorithm used, which in turn dictates the strength of the key.

• ENCRYPTION BY: Defines the protection method of the key. The key used in the ENCRYPTIONBY argument can be a certificate or an asymmetric key that is located in the Master database.

Listing 6 shows the exact command used for the HomeLending database's database encryption key.

Listing 6. Creating the HomeLending database encryption key.

The AES_128 option specifies Advanced Encryption Standard (AES) with a 128 bit key length, and we protect the database encryption key with the MasterCert certificate that was created in the Master database.

The final step in the setup process of TDE is to enable it. This is accomplished by executing the ALTER DATABASE command with the SET ENCRYPTION ON argument.

Listing 7. Enabling TDE.

At this point, an encryption scan occurs, which is the process by which the physical files of the database are scanned and encrypted. Included in this scan process are the database files, TempDB database files and transaction log files.

Transaction log files contain information that is used to maintain data integrity and are used in the restoration process. Within these files are a series of smaller units called virtual log files (VLFs). These VLFs contain records that pertain to transactions within the database file. Prior to the implementation of TDE, these VLFs contain unencrypted data. During the encryption scan any pages that have been in the buffer cache and modified, known as dirty pages, are written to disk, a new VLF is created and the prior inactive VLFs are truncated. This results in a transaction log that only contains encrypted data.

The duration of the encryption scan will vary depending upon the size of the database files. Once the process has completed, the encryption_state column in the sys.dm_database_encryption_keys dynamic management view will reflect the encryption state of "encrypted," and will show the value of "3" in this column, for our HomeLending database.