OPASS: Transparent Data Encryption the Lightning Round!

I’ll be presenting at OPASS on March 8^th at 6:00 pm CLICK HERE to register to attend the meeting.

This is just a preview presentation so I won’t be deep diving the topic, we’ll just be scratching the surface.  So I’ll start by talking about Transparent Data Encryption, what is it?

WHAT IS IT?

Let’s break down the name into its three parts.

Transparent-The process is Transparent to everything going on in the server.  When you turn this on you do not need to make any changes to the way an application access the data.  As far as the Application knows this is the same database it was before it was encrypted.

Data-The data itself is affected. “So Balls,” you ask “’The Data’ is a pretty wide open term, what do you mean by ‘The Data’?”  Great question!  Any way in which SQL Stores Data is encrypted when Transparent Data Encryption is turned on, for the Database that it is turned on for.  This is a Database Level Technology similar to how Mirroring is a Database Level Technology.  Your Datafiles are encrypted and your Log File is encrypted  (VLF’s are encrypted starting when encryption is turned on, VLF’s previous to that will get encrypted when they are overwritten).

Encryption-There is some flexibility in the type of encryption Algorithm that you can select, during my demo’s I’ll be using AES 256 for my encryption level.

Need a little more?  Okay on to the next analogy.

PHYSICAL DATA SOLUTION

Seeing as how I’m a comic book kind of guy, the best analogy I can give is the X-MAN Colossus.  Transparent Data Encryption is a Physical solution, your .MDF, .NDF, .LDF, .BAK, .TRN, and .DS (Snapshot files) will be encrypted on the disk while at rest.

So on the outside your armor is on, inside (when data is in motion) however your flesh and blood.

DATA IN MOTION

“So Balls,” you say “What is this Data in Motion?”  Well Data in Motion just means that the data has been fetched from Disk by the Storage Engine, and is cached in Memory.  So while this is Transparent to all the applications that point to your database, you must keep in mind that SQL Server is an application as well.  And if you have access to read the data before you encrypted it, you have access after.  If you are looking for Transparent Data Encryption, TDE, to provide complete row level encryption after the data is fetched, then you need to be looking at Column level encryption.  TDE will only encrypt data at rest on the disk.

WHY USE IT

This is the Fifty Million Dollar question, and I’m going to give you a preview of my slides.

When Data is stolen it effect’s not just you the DBA, Your Customer's Identities, your Businesses reputation, and the financial toll to your Business as well.

Nobody wants to be the DBA in any of those headlines.  I’ve been in the situation where there have been security breaches, and every single gap you can cover is one that you are glad you did.  If your server is breached  you could find yourself in a room with a CIO, VP’s, Directors, Civilian Leadership, a General, Base Commander, Congressional Committee,  your Project Manager, or anyone one else in a position of authority.  In that moment what would you like to say?  My database(s) had Transparent Data Encryption on them, or we passed on having TDE on the server for [name the reason, and  you WILL really need to justify it].

 Sometimes this won’t be up to you.  You can recommend it and your business can choose to pass.  All you can do is be ready, because if you stay in the business long enough eventually you’ll be in this situation, *IF you’re lucky enough to catch the breach.

In the presentation we’ll cover what a datafile looks like in an unencrypted vs. an encrypted state, how to turn TDE on and how to turn TDE off.  How it effects the Tempdb, backup compression, and some tips and scripts for self managing TDE Certificates.

I’ll have a follow up post later in the week after the presentation.  I hope to see you there!

Thanks,

Brad