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.
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
USETESTDB;
GO
CREATEMASTER 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 DATABASETESTDB
SET ENCRYPTIONON
GO
