Transparent data encryption (TDE) is available only in SQL Server 2008 R2 Enterprise Edition. This mechanism is used to encrypt/decrypt data and log files in real time. The set up involves:
1) Creating a master key
2) Creating a database certificate
3) Creating a database encryption key
4) Enable encryption on the database
Master key (a symmetric key) is created as shown below
Querying the sys.symmetric_keys would retrieve all the symmetric keys in that database instance. In the below screenshot it shows us the master key has been created.
The next step after creating the master key would be to create the database certificate which is as shown below:
The certificate information can be retrieved by querying the sys.certificates which is as shown in the below screenshot:
Now the next step would be to create a database encryption key by the server certificate as shown below:
The database encryption key is created on the database that we are trying to encrypt and in this case this is AdventureWorks2008R2 database. Encryption on the database is enabled as shown below:
In my next post I will discuss how TDE encryption works and how to perform the decryption.
Hope this helps!