PageViews Last Month

Tuesday 13 October 2015

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. 


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




5 comments:

  1. The simple term of encryption is now known as "anonymyzation of data". A simple yet informative read, thanks!

    ReplyDelete
  2. The simple term of encryption is now known as "anonymyzation of data". A simple yet informative read, thanks!
    Bellwether
    ISO 27001 Consulting Company

    ReplyDelete