SQL Saturday 74 Jacksonville

Hello Dear Reader, If you're visiting looking for SQL Saturday Slide Decks and info please go to the Resources page, all info is there!  Just Click Here, and come back on Monday for my SQL Saturday Roundup!

Thanks,

Brad

Transparent Data Encryption & Backwards Compatibility

We were working with Chris Mitchell (Twitter) from the Microsoft Technology Center in Atlanta the other day, out of the Microsoft Office in Tampa.  The Office Has a Beautiful and very distracting view, see the picture below, of the Bay area, my friend Dan Taylor(blog|Twitter) took the photo.

One of those views that makes you love living in Florida!  

But I digress, we were having a really great conversation about features that are enabled or disabled when you enter a database into 80 Compatibility level, but are running on a 2008 R2 Instance.  

When having discussions you typically throw things out by features (or at least that is what I’ve heard people do), Database Compression will not work, Filestream will not work, Encryption will not work.  But when we brought up encryption I asked does that mean Transparent Data Encryption will not work.  Neither of us knew off hand.

I’ve presented on this topic so I’ve got some scripts on standby, so I opened one up fired it off, changed the Compatibility Level, and it still worked. 

“So Balls”, you say “Prove It.”

Dear Reader I wouldn’t have it any other way!

AND….. HERE….. WE…… GO!

First we’ll create our Demo Database

/*

Create TDE Database for demo

*/

USE master

GO

IF EXISTS(select * from sys.databases where name=N'TDE')

BEGIN

    DROP DATABASE TDE

END

CREATE DATABASE TDE

GO

When we use Transparent Data Encryption we need to First Create a Master Key and a Certificate in the Master Database.

/*

Create the Master Key for the

SQL Server

*/

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='brad1'

GO

/*

Create the Certificate that will be used

for database level encryption

*/

CREATE CERTIFICATE DatabaseCertificate WITH SUBJECT='NunyaBiznes'

GO

Now that we’ve got those let’s alter our databases compatibility level to 80, SQL 2000.

USE TDE

GO

/*

Let's Alter Our Database

And Place It in 80,SQL 2000,

Compatability Mode

*/

ALTER DATABASE TDE

SET COMPATIBILITY_LEVEL = 80

Now let’s Create our table, and Insert some data.  You’ll see that I have default values set up to simulate an SSN. 

/*

Create Table for filler data

*/

IF EXISTS(SELECT * FROM SYS.tables WHERE name='tdeData')

BEGIN

    DROP TABLE dbo.tdeData

END

CREATE TABLE dbo.tdeData(

    ID int IDENTITY(1,1) NOT NULL

    ,nameText varchar(100) default 'fakeSSN'

    ,ssnText varchar(100) default '111-11-1111'

    ,fillerText char(5000) default 'a'

)

GO

/*

Create filler data for TDE demo

*/

DECLARE @i int

SET @i = 0

WHILE (@i < 15000)

    BEGIN

         INSERT INTO tdeData DEFAULT VALUES

         SET @i = @i +1

    END

I do this in Demo’s because I want people to see that when you insert data into a Data File or a back it up to a Backup File, the data is in plain text.  That is part of the reason you use TDE, because it adds an additional layer of protection.  So let’s backup our data Pre Encryption, and look at it in a Hex Editor.

Look at that there is our social security number 111-11-1111!  Now let’s double check our Compatibility Level. 

And now let’s enable encryption. We create a Database Encryption Key using our Certificate we made earlier, and specify our algorithm.  Then we set the database encryption to on.

/*

Create Database Encryption Key

*/

USE TDE

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseCertificate

GO

/*

Set The Encryption On

*/

ALTER DATABASE TDE

SET ENCRYPTION ON

 We’ll use a DMV to validate the encryption process that SQL MVP Jasper Smith(blog) wrote that I’ve loved to use for years now.

SELECT

    CASE e.encryption_state

                WHEN 0 THEN 'No database encryption key present, no encryption'

                WHEN 1 THEN 'Unencrypted'

                WHEN 2 THEN 'Encryption in progress'

                WHEN 3 THEN 'Encrypted'

                WHEN 4 THEN 'Key change in progress'

                WHEN 5 THEN 'Decryption in progress'

    END AS encryption_state_desc,

      e.percent_complete,

    DB_NAME(e.database_id) AS DatabaseName,

         c.name as CertificateName,

            e.encryption_state

    FROM sys.dm_database_encryption_keys AS e

    LEFT JOIN master.sys.certificates AS c

    ON e.encryptor_thumbprint = c.thumbprint

   

You just execute this after you set Encryption On and watch the internal process work, the size of the database will determine how long it runs.  For a large database this is a great DMV to show you just how long the process is taking.  And now we are encrypted! 

So let’s do another backup and open that up in a Hex Editor to see if we can find our SSN.

And as you can see our file space looks quite a bit different, and we couldn’t find the string for our SSN

WHAT IN THE NAME OF PRO-WRESTLING DOES THIS MEAN!?

First off it means that if you have to keep your database in 80 Compatibility Level, you can still use Transparent Data Encryption to secure your backups and your Data Files at rest. Your log files will be encrypted too, but this occurs at a VLF, virtual log file, level.  So the full log will not be encrypted until all VLF's have been over written.

Some other pretty cool possibilities, I asked Paul Randal (blog|twitter) what part of the Storage Engine handled the Encryption and Decryption of pages. 

I know Access Methods handles Compression, because the pages are stored in memory in a Compressed State before being passed back to the Relational Engine.  But Pages are decrypted when stored in memory?  So I didn’t think it was the Access Methods. 

Paul confirmed that it was indeed the Buffer Manager that handles TDE. 

So that leads me to think some ROCKING things may be possible, if you know what the Buffer Manager IS COOKIN!

Thanks,

Brad

SQL Saturday 74 Jacksonville, Last Stop before SQL Rally

This Saturday is SQL Saturday #74 in Jacksonville, FL.  This will be the last time that I present on Data Compression in SQL Server, before SQL Rally in May.

This will be my first time in Jacksonville, but I’ve been told by many experienced SQL Saturday Alumni that the location is beautiful, and the event is always a good one.  We are on the University of North Florida Campus, in the College of Computing and Engineering Building, for the address Click Here. 

There is a great line up for this SQL Saturday.  You’ve got great Community Leaders & MVP’s like Kevin Boles (twitter), Andy Warren (blog|twitter), Plamen Ratchev (blog|twitter), Kendal Van Dyke (blog|twitter), Don Stevic (blog|twitter), Adam Jorgensen (twitter), Jose Chinchilla (blog|twitter), Troy Gallant (blog|twitter), Brian Knight , and many many more.  I also have it on good authority that this Balls fellow will be there.  For the full event schedule Click Here.

So far over 450 people have pre-registered to Attend, and you can be one of them the cost is A WHOLE LOTTA NOTHIN!  That’s right Dear Reader, the only cost is your time, you showing up and getting free training in SQL from the cast listed above, Click Here to Register.

This is also the last SQL Saturday in the US before SQL Rally, and I’m psyched. 

“But Balls,” you say, ”What if I’m not going to SQL Rally?”

Even more reason to attend!  Some of the presentations given today will be given at the Rally!  Not only that, but your feedback can help make the SQL Rally better.  

Every presentation I do I look for feedback to figure out if there is anything for my presentation that I’m missing or could be doing differently. 

So come out and enjoy a day of free training with your peers, discuss SQL, and if you find a place to leave feedback help influence the presenters to be better at what we do!

Thanks,

Brad