Thank You OPASS! Transparent Data Encryption Recap

At the start of March I had the great pleasure of being the opening DBA shot for the OPASS meeting.  Microsoft MVP Ken Tucker was the main act giving us an inside look at Silverlight MVVM Framework.

Going to OPASS is like going to the Oscar’s, the stars in the audience are part of the treat!  Everyone had great questions, and they were very interactive.  Jack Corbett (blog | twitter) and Karla Landrum (twitter) had a wonderful venue, Rikka Asian Bistro, for the OPASS meeting.

During my Talk I had promised to post my slide deck and the script that I used for my Demo, so I wanted to do that right off the bat.

Slide Deck – OPASS Transparent Data Encryption: The Lightning Round

Demo for the presentation

DEMO RECAP

So Dear Reader during the talk we covered a couple things.  We covered Why you should use Transparent Data Encryption (TDE), How to setup TDE, how to Disable TDE, and the effects TDE has on your backup and recovery Strategy, the slide deck and demo’s are attached so I’ll let you go through those on your own I’m just going to cover things from a logical level.  We’ll step through each of those, and then we’ll get to some advanced scripts on how to self manage certificates for TDE.

WHY?

Here is a slide from my deck, and this is the best reason why, if you are a DBA long enough eventually you will find yourself in a situation where a backup is lost, something will get stolen, or something will get hacked.  TDE will not save you from every scenario, but what it will do is make so when you are in this situation you have one more level of protection. 

“But Balls,” you say “This only covers your back-side as the DBA, why would my Boss care about this when it’s me on the line?”   

Well Dear Reader yes it is our back-side on the line, but so is our Boss’s, and the companies.  If you take the point of view of Jerry Macgwire “help me, help you. HELP ME, HELP YOU!”. 

Don’t go and yell at your boss, but you should know them well enough to know to find an approach that they will respond to.  If its ROI, bring up how much companies lose in money because of incidents like these, and future loss of customers profit if customer’s confidence in the business is shaken. 

HOW TO TURN IT ON

Get your minds out of the gutter J.  Turning on TDE is very easy we just follow 4 easy steps

1. In the Master Database Create a Master Key
2. In the Master Database Create a Certificate
3. In the User Database to be Encrypted Create a Database Encryption Key
4. Alter the User Database and Set Encryption On

It’s that easy.  After step 4 an encryption scan will begin on the database.  It is safe for user activity.  The only things you cannot do is alter the database at the Filegroup level.  No dropping, no adding.  While you are encrypting your database, It’s probably not the best time to be doing changes to the underlying file structure, (one thing at a time!).

Keep in mind that the second the 1^st user database becomes encrypted with TDE the TempDB will be encrypted as well.

TURNING IT OFF

“So Balls”, you say, “I turned TDE on my DEV system, did a demo for my boss, we’re not going to use it, how do I turn it off!”  We’ll Dear Reader it is pretty simple.

1. Alter All user databases and Set Encryption Off
2. Drop the Database Encryption Key for each User Database that was encrypted
3. (To Unencrypt the TempDB)-Restart the SQL Service
4. *DO NOT DROP THE CERTIFICATE

BACK THAT THANG DATABASE UP

When you turn on TDE it affects all Datafiles at rest.  All Data Files, Log Files (only the VLF’s utilized while TDE was turned on), snapshot files, and backup files.  So here are the things to consider.

If you turn on TDE on your Prod system, and you want to do a data refresh to your Dev or Staging environments you will need to restore a copy of the Certificate used to encrypt your Prod databases to your Dev or Staging server where the restore will take place.

If you want to use Log Shipping or Database Mirroring you will need to have the Certificate you use on your Primary or Principal Server on your Secondary or Mirror Server.

So if you have a Database using TDE, if you are using a technology that uses Restore and Recovery then you will need a copy of the Certificates on each SQL Instance where a copy of the Database will reside.

Now keep in mind this doesn’t mean you can’t move data around.  TDE works with data at rest.  That means that if you BCP data out of the database it is in an unencrypted state.  You don’t need to have a copy of the Certificates in your destination. 

So how does this effect your recovery strategy?  You need to backup your Certificates regularly.  You need to make sure that you plan for them like you do your backups.  If you sweep your backups to tape you’ll want to sweep your certificates to tape.  If you back up your databases to disk, backup your certificates to disk.  Keep redundant copies.  If you ever have to restore your database to a different server you will need them. 

I’ll be doing covering Key Management in my next post but all of the scripts you will need are in the download.

Each of these scripts need a little tweeking, but all the information is there.

Local DB, function, table, and encryption.sql (Sets Up a local DB, and TDE, to store passwords used in later scripts)

backup tde certificates.sql (T-SQL Script to automate certificate backups)

delete old TDE certificates.sql (T-SQL Script to automate certificate clean up)

Hope these get you on your way to learning how to use TDE!

Thanks,

Brad

T-SQL Tuesday 16: Aggregation

So it’s that time again, T-SQL Tuesday.  This week our event is Sponsored by Jes Borland (Blog|Twitter).  So Jes what’s the subject?

“I want to hear how you solved business problems with aggregate functions. I want to see your cool T-SQL tricks. How are aggregates used in SSRS, SSAS, or SSIS? What have you learned about aggregate functions?”

So Dear Reader, Aggregation it is!

SOME THINGS JUST GO TOGETHER

We’ve all probably done our fair share of fancy aggregates, I have to say that most of the code I’ve used aggregates for is for business functionality and sitting with clients that I’ve worked for in the past.  I think we all have a nightmare report, script, or task that we accomplished using aggregates and while those things make for interesting stories, they aren’t that useful.  I’d like to give you something that you can use, (if you don’t already have a script to do what I’ve done here). 

Sometimes the most useful things are simple, like PB & J (simple and delicious!).  Here are a couple of queries that I use that have the SUM aggregate function in them.  Simple but effective.

The first is a quick script that extracts the information from DBCC SHOWFILESTATS and uses the "undocumented" sp_msforeachdb in order to gather total space used by all datafiles on an instance, total space reserved for all data files on an instance, then a breakdown by datafile per database, lastly we take the info from DBCC SQLPERF(LOGSPACE)and sort by the log file size.

The second script is of the same concept.  You can use the built-in reports “Disk Usage by Tables” to get the same info, but sometimes it’s just nice to build a script to work for you.  This script uses sp_spaceused and the "undocumented" sp_msforeachtable in order to build a list of tables by row number and the Reserved size for Data. (I use the Second script on my Compression demos to help you find an appropriate table (size wise there are multiple steps J) while examining compression).  Then I do a SUM function on each of the fields to show an aggregate of your database by user table.

SCRIPT1 – Get Database Size Stats

/*============================================================

--@SQLBalls: SQLBalls@gmail.com

--http://www.SQLBalls.com

--T-SQL TUESDAY 16 Aggregation

--

--This Sample Code is provided for the purpose of illustration only

--and -is not intended to be used in a production environment. 

--THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS"

--WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING

--BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR

--FITNESS FOR A PARTICULAR PURPOSE.

==============================================================*/

IF exists(SELECT * FROM tempdb.sys.tables WHERE name like '#myTable%' and name not like'#myTable2%')

BEGIN

    DROP TABLE #myTable;

END

CREATE TABLE #myTable (

                      [myID] [int] IDENTITY(1,1) NOT NULL,

                      fileid varchar(4),

                      fileGroup varchar(4),

                      TotalExtents int,

                      usedExtents int,

                      DBName sysname,

                      filename1 varchar(8000));

GO

                     

IF exists(SELECT * FROM tempdb.sys.tables WHERE name like '#myTable2%')

BEGIN

    DROP TABLE #myTable2;

END

CREATE TABLE #myTable2(

                      dbName sysname,

                      logSize float,

                      spaceUsed float,

                      dbStatus int

                      );                   

GO

INSERT INTO #myTable

EXEC ('sp_msforeachdb @command1="use [?]; dbcc showfilestats"');

GO

                     

INSERT INTO #mytable2

EXEC( 'dbcc sqlperf(logspace)');

GO

SELECT

((SUM(usedExtents)*64)/1024) AS DatabaseUsedSpaceinMB

,((SUM(totalExtents)*64)/1024) AS DatabaseSizeinMB

FROM #myTable;

GO

SELECT

    DBName

    ,usedExtents

    ,TotalExtents

    ,((usedExtents*64)/1024) AS DatabaseUsedSpaceinMB

    ,((totalExtents*64)/1024) AS DatabaseSizeinMB

    ,(((totalExtents*64)/1024)-((usedExtents*64)/1024)) as DataFileFreeSpace

 FROM

    #mytable

 ORDER BY

    ((totalExtents*64)/1024) DESC;

 GO

SELECT

    *

FROM

    #myTable2

ORDER BY

    logsize DESC;

GO

DROP TABLE #myTable;

DROP TABLE #myTable2;

GO

SCRIPT2-Get Table Size Stats

/*============================================================

--@SQLBalls: SQLBalls@gmail.com

--http://www.SQLBalls.com

--T-SQL TUESDAY 16 Aggregation

--

--This Sample Code is provided for the purpose of illustration only

--and -is not intended to be used in a production environment. 

--THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS"

--WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING

--BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR

--FITNESS FOR A PARTICULAR PURPOSE.

==============================================================*/

IF EXISTS(SELECT * FROM TEMPDB.sys.tables WHERE name like '#myTable%')

BEGIN

    DROP TABLE #myTable

END

CREATE TABLE #myTable(

                               [Name] sysname,

                               [Rows] int,

                               [Reserved] varchar(500),

                               [Data] varchar(500),

                               [Index_Size] varchar(500),

                               [Unused] varchar(500)

                               );

INSERT INTO #myTable

EXEC ('sp_msforeachtable @command1="sp_spaceused [?]"');

GO

SELECT * FROM #myTable

ORDER BY

    [rows] DESC,

    LEFT([reserved],(LEN([reserved])-3)) DESC;

SELECT

    SUM(CAST(LEFT([reserved],(LEN([reserved])-3)) as int)) as [Total_Reserved(KB)]

    ,SUM(CAST(LEFT([data],(LEN([data])-3)) as int)) as [Total_Data(KB)]

    ,SUM(CAST(LEFT([Index_Size],(LEN([Index_Size])-3)) as int)) as [Total_IndexData(KB)]

    ,SUM(CAST(LEFT([Unused],(LEN([Unused])-3)) as int)) as [Total_UnusedSpace(KB)]

FROM  #myTable

GO

DROP TABLE #myTable;

  

Happy T-SQL Tuesday I hope you enjoy the scripts!

Thanks,

Brad

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