SQL server Encryption – Transparent Data Encryption

In the previous blogs we saw the use of certificates to encapsulate symmetric keys. How it benefits the user by maintaining the password rather than user having to mention it every time a key is opened for use.

In all the above methods we are protecting data at a granular level like tables and columns. 

http://sqlandmorewithkruti.blogspot.co.uk/2015/10/sql-server-encryption-certificates.html

What if we wish to protect data at the higher level?  Let’s say no one should be able to see any data in my database until I authorize to do so. I wish to protect all my data which implies all the data that resides in my database files and log files. From SQL server 2008 this functionality was provided through Transparent Data Encryption. As per definition by Microsoft, TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery.

Isn’t it amazing even if accidentally someone were to get access to the data and log files they would be able to attach the database and view the information. Or let’s say they get access to the backup files they still wouldn’t get the data by a normal restore operation.

The encryption of files and decryption happens by SQL server in memory. The data at rest is cipher text. It does impact the performance while these operations are performed in memory. Pages are first decrypted and then the data is provided to the receiving client.

How it happens

1)  CREATE MASTER KEY

USE TESTDB;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1211!pwD##';

GO

2) CREATE CERTIFICATE

CREATE CERTIFICATE TestCertificate

 WITH SUBJECT = 'For TDE for TESTDB’,

 GO

3) CREATE DATABASE ENCRYPTION KEY

     

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TestCertificate

 GO

4) ALTER DATABASE SET ENCRYPTION ON

ALTER DATABASE TESTDB

SET ENCRYPTION ON

GO