TDE – Page 2 – MSSQLTREK

This is the second part of the Encryption Series, In this Blog post let’s see what can be achieved within SQL Server in terms of Encrypting the Data.

In Legacy days……..Prior to SQL Server 2008(Enterprise Edition), we only had an option to encrypt at Column level/Cell level not at the Database level. Even in SQL Server 2008 if you are using standard edition of SQL Server, you don’t have an option to encrypt at Database level. So…Basically we’ve to make this possible manually on every column by making use of either of the following inbuilt functions which SQL Server offers us..

EncryptByPassphrase()

EncryptByCert()

EncryptByAsymKey()

EncryptByKey()

So…basically you’ve to deal with all the annoying Symmetric Keys/ Asymmetric Keys/PassPhrases etc..and you’ve to make sure that the routine is followed each time you add a new column or make any change to your table…and we’ve to make use of Decrypt routine while retrieving the data, which is a huge hugeeeeeeeeee deal especially for developers!! You are dealing with tons and tons of Code changes and huge huge amount of testing is required!(Also a hugeeee…CPU hit depending on how many columns you are encrypting)

If you are on Enterprise Edition of SQL Server 2008, we’ve a brand new feature TDE(Transparent Data Encryption) which is at Database level and completely transparent to developers or anyone who is actually accessing our Database!! Nothing needs to be done from developers standpoint, basic testing is necessary though(Since it could cause 3-8% CPU hit under the hoods as per Microsoft documentation). All the Encryption and Decryption routines are completely transparent:) which is AWESOME!

How to Enable TDE???

1. Very first Step would be creating a Master Key in Master Database.(Immediately Backup your Master Key)

2. Now, create a Certificate(in Master Database) based on this Master key.

3. Now, create a Database Encryption Key(In the Database which we are trying to Encrypt) and the final Step would be Turning on Encryption at Database level.

Note:

If you try directly Enabling TDE on a Database without a DB Encryption Key you’ll be yelled at with the below Error Message

Msg 33106, Level 16, State 1, Line 1

Cannot change database encryption state because no database encryption key is set.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Demo:

Please see the below Screenshot which covers all the Required Steps to Enable TDE on a given Database.

Technically, That’s all we need to do for encrypting a Database! Pretty Simple huh?

Note: You’ve to provide a very very Secure Password(I would say 20-30 Characters including all kinds of Alpha Numeric Combinations and store it in a safe) for your Master Key.

How to Verify whether your database has been enabled with Encryption?

You can Go to Database Options from SSMS as shown below or Just Querying the Sys.Databases view!

SSMS :

T-SQL:

Int the next post of this Encryption Series, let’s see what are the challenges we’ve while dealing with Encrypted Databases! There are quite a few things to keep in mind as a DBA in order to Support TDE enabled Databases.

This is the First part of the Series Which Covers how to handle the Encryption piece in SQL Server. The Term “Encryption” is one of the Most Interesting and annoying things which we as DBA’s see, especially if we never dealt with Database Encryption,Certificates, Keys in the Past and all of a sudden we got a request from the Application team saying “Hey My Dear DBA! Our Database for this Application is going to contain PII(Personally Identifiable Information) Such as SSN, Address etc and should be SOX (Sarbanes-Oxley Act) Complaint, and we need the Data to be Encrypted. What are the Options we’ve in SQL Server?…” So what are the Options we really have in SQL Server?

Well, In this part of the Encryption Series let’s try to explore what are the Options we’ve to Encrypt Data from Windows Server Perspective. In the future Posts I’ll be showing you what are the options from SQL Server stand point. For now..Let’s look at basic Windows Encryption techniques.

Encrypting File System(EFS):

EFS basically is at File(s)/Folder(s) Level. It Encrypts the Files or Folder(s) based on the User accounts. It doesn’t Encrypt all the contents of the Drive independent of any user. let’s say…you’ve multiple User accounts or groups, they can individually encrypt their own Files and Folders Independently. You need not be even an Administrator on the Server to implement EFS and encrypt your Files!! No Special Softwares, no special Hardware pieces required to be attached to your motherboard! It’s pretty Basic level of encryption, in which all your encryption keys are stored in your OS Drive(typically C$). So, what if hacker is too smart and got into your C$?? yes, He/she can decrypt all your encrypted stuff in no time!!

Bitlocker Encryption:

Bitlocker is at Drive/Volume level. It encrypts all the contents(files/folders) on all the Drives Including System Files in Operating System Drive and Removable Drive(s) as well! It’s independent on User accounts. The Contents will be encrypted irrespective of the User account(s). i.e, Once Bitlocker Encryption is turned ON, all the Files/Folders will be Encrypted for all the Users. You have to be a a member of Local Admins Group at minimum to turn on Bitlocker Encryption and it requires a special hardware piece called Trusted Platform Module(TPM) to encrypt the contents at Drive level. Since the OS Drive itself is encrypted, for a hacker even if he/she got hold of C$, they really can’t get into Encryption Keys.(So..It’s quite possible, we can use EFS to let Users to encrypt individual Files and Windows Server Admin Encrypts the OS Drive using Bitlocker. Few Companies use this Technique as their Security practice)

As per the Microsoft Documentation – Enabling EFS for SQL Server Database Files is not a good idea and they both together doesn’t work well in terms of performance. They’ve(EFS and TDE – Transparent Data Encryption, this is something which we enable at SQL Database level which I’ll be exploring in future posts) concurrency Issues working together! Bitlocker for SQL Server(Bitlocker and TDE) has no noticeable concurrency issues working together. If you reallyyyy…need that level of Encryption, Yes you can definitely think Bitlocker/TDE combination as an efficient solution.