Showing posts with label TDE. Show all posts
Showing posts with label TDE. Show all posts

Tuesday, September 10, 2013

T-SQL Tuesday #46 Rube Goldberg Machine aka Automating Deleting Older Certificates




Hello Dear Reader!  This is the second Tuesday of the month and you know what that means, T-SQL Tuesday the largest blog party on the Intrawebs.  T-SQL Tuesday is the brain child of SQL Community member extraordinaire Adam Machanic(@AdamMachanic | Blog), also the inventor of the word “Twote”  as in “To misquote a Tweet”, when used in a sentence it sounds like “He Twoted me wrong”.  This month our host is Rick Krueger(@DataOger | Blog).  So Rick what’s our topic?

My first exposure to Rube Goldberg Machines was playing the game Mouse Trap as a child. I work almost exclusively on the SQL development side of the house, where we sometimes build crazy creative solutions to solve business problems. We generally know the ‘right’ way to do things, but pesky issue like budgets, personnel, and deadlines get in the way. So, we channel our inner MacGyver, grab a handful paper clips and some duct tape, and then do things with SQL Server that we know shouldn’t be done (in an ideal world). And we hope nobody ever finds out how we bent the rules, because we know they will judge us (as we would judge them) and call our work a <gasp>HACK</gasp>.
So, if you would please, dust off one of those skeletons and tell us how you got really creative with SQL Server, instead of doing it ‘the right way’. In other words, tell us about your ugly SQL baby. If you’re worried about saving face, feel free to describe how you would have implemented the solution if you lived in that ideal world.”
I love mouse trap and MacGyver!  Over the years as a DBA sometimes you have to work with what you’ve got.  Other times your boss says do A, you say the best way to achieve A is by doing B & C and they say do A.   I’ve got two of these that I can think of off the top of my head.  One we used Change Data Capture in lieu of Auditing (don’t ask me why, because that version of SQL also had Auditing.  Oh Hello A…..).  The other may actually prove useful.  Which one to choose from?
“So Balls”, you say, “What’s the useful one?”
Good call Dear Reader, we’ll go with the useful one!

OUT OUT D@MN SPOT CERTIFICATE

When you are using Transparent Data Encryption one of the most important things is the certificate.  Once you enable it on a production database that certificate is just as important as your database backup.  Why?  Because in case of a catastrophic failure that backup is dependent on the certificate.  If you cannot restore the certificate to a new instance your backup is useless.  *There are some work arounds to this using backups of the Master DB, but we’ll save that for another day.*

When you look at setting up maintenance plans for your server you should create a job to back up your certificate daily.  A certificate is only 1 KB in size.  Very tiny file.  If you use a private key to encrypt your certificate it is only 1 KB in size as well.  So if you leave a year of them on your hard drive you haven’t taken up 1 MB.

As a DBA sometimes you can be anal retentive a neat freak.  I don’t keep a year’s worth of backups on hand, why would I keep a year’s worth of certificates on hand?  I’d like a process to automatically delete them and only keep the last two weeks on hand, or month on hand whatever matches up with my backup retention policy.

The problem is the automated cleanup task doesn’t work.  Sure you can go in the maintenance plan wizard, make one that looks in a directory for a .CER file, but the true problem lies in the data storage.  You have to custom script out the certificates.  If you didn’t think to add a line to the backup set history table with the extension of .cer and .key and the path to your Private Key or Certificate backups then the job won’t work.

Inserting records into the MSDB tables could work, but as a DBA new to TDE that thought hadn’t crossed my mind.  I wanted a way to back up my certificates and delete my old ones.  So I built one.

MY RUBE GOLDBERG MACHINE

This is a demo I do in my TDE presentation.  It’s up on my Resource Page and has been for some time.  Today I realized I’d never blogged about it.  My scripts heavily use XP Command Shell.  I had an audit setting in my environment that wouldn’t allow that to be on my servers.  So in this script I turn it on in the beginning and off at the end.  The nice thing about the script is I unit tested it and even if there is an error in the script the sp_configure settings are server level commands that occur outside of transactions, so they run no matter what.  The script runs quick, but it will make logged entries in the SQL Server Error log stating that XP_Command shell was turned on and off.  My audit team could live with this so I was able to implement it.
I also like to use a private key and a password for my TDE Encryption.  I don’t want the password sitting around in plain text in the job either.  So I make a database called TDE.  In it I have one table called tdeKeys.  I put two columns in there one is the name of my certificate that a private key will be created for the other is the password to use for that private key.  In secure environments you could set up column level encryption to ensure the password is not in plain text even in the table field.  The demo scripts I’m going to give you doesn’t use column level encryption.  It contains a function that retrieves the Password for the Certificate Name.
Next we will create the dynamic script to back up the certificate.  Note that I backup the Master Key as well.  If you are using column level encryption you’ll want a copy of the Master Key.  You’ll need to specify the path that you want to back up the certificates.  Also you will need to specify the certificate name.
Finally we will create the script that will use xp_cmdshell to transverse directories to manually delete our backups.  You will need to edit the file path in this script and insert the Master Key and certificate names in line 74.  Finally on line 103 you will need to alter the DATEADD function.  Right now it would only keep 4 days of certificates on hand, you’ll need to edit the DATEADD to match up your backup retention policy.

Want to see the whole presentation live?  I’ve done this for Pragmatic Works Training on the T’s, click Here to watch.  You’ve got to sign up for a Pragmatic Works account if you don’t already have one, and you’ll get free info on all the free training we do monthly!


OVERKILL

“So Balls”, you say, “Is this over kill?”
<soapbox>
Well Dear Reader it depends on your environment.  You must consider Level of Effort and Level of Response, LOE and LOR.
LOE is one part you one part the hacker.  The more secure you make something the less likely that a hacker will keep going for it, or how far they will bother to go.  On your part it is how far you are willing to go to do your job.  We can also get dissuaded from going the extra mile sometimes.  Your LOE should be governed by your organizations LOR.
LOR is the response that your organization will have to the event.  One thing I like to tell folks is that if you are ever in the position that your security has been breached, and you are then talking to your boss, his/her boss, the CIO, a high ranking officer in the military, or a/multiple high ranking government official(s).  Trust me when I say that you want to be able to say you took every last step possible to protect the data under your care.  The more detail you can provide the better.  So overkill?  Maybe.  CYA.  Absolutely. Thankful that no fault on your part was found and you still have a job? Yep.
Having been in this position trust me take the extra couple steps, if you ever need it you’ll be glad you did.
</soapbox>
Thanks to Rick for hosting this today, and as always Thank You Dear Reader for stopping by!
Thanks,

Brad

Tuesday, August 14, 2012

Database Certificates and the X.509 Standard



 Hello Dear Reader, I came across an interesting discovery about a year ago and realized I’d never written about it.  I’ve done a lot of work with encryption and mainly Transparent Data Encryption.  I’ve got a presentation that I’ve given on the subject that I presented at SQL Saturday’s, Dev Conection, and SQL Rally.  I take a database, back it up, and drop the unencrypted backup in a hex editor.  This allows me to show what the contents look like before and after encryption.

I encrypt the database and take a backup and I put that in the hex editor as well.  One day in front of OPASS, the North Orlando SQL Server User Group, I dragged the certificate and private key backups in the hex editor as well and I noticed something disturbing.  Part of the encrypted backup of the certificate was in plain text!

“So Balls”, you say, “What does the certificate have to do with the X 5 O….whatever.”

Well put Dear Reader, and the short answer is again everything.

BETTER LOCK THAT $H!+ UP

The X.509 Security standards are the International Technology Union encryption guidelines for Public Key Infrastructure and Privilege Management Infrastructure.  In short these are the smart guys that make up the encryption standards we use in just about everything.   It just so happens that they have some pull over SQL Server Database Certificates as well.

So I was in front of OPASS and I was giving a presentation on Transparent Data Encryption when I made an interesting discovery.  I made all my demo’s and passwords easy so I wouldn’t have to worry which was which,  the password was ‘Brad1’.  Imagine my surprise when I pulled in the encrypted, with a private key and password (aslo ‘Brad1’), backed up contents of the Database Certificate and found laying there in plain text was ‘Brad1’.

It was my own fault for making a demo that used the same thing over and over.  I didn’t know which password leaked.  I went home entered a different value in for each place I had ‘Brad1’, backed up the certificate, and pulled it into a hex editor.  It was the Subject of the Certificate.

DON’T PUT ANYTHING IMPORTANT IN THE SUBJECT

Why would the subject be in plain text?  Good question Dear Reader.  I hopped over to MSDN to look at the documentation on database certificates, click here to view.  I found this information:

                SUBJECT ='certificate_subject_name'
The term subject refers to a field in the metadata of the certificate as defined in the X.509 standard. The subject can be up to 128 characters long. Subjects that exceed 128 characters will be truncated when they are stored in the catalog, but the binary large object (BLOB) that contains the certificate will retain the full subject name.

Nothing about why it was in plain text, but it pointed to the X.509 Security Standards.  Click here to read the X.509 Security Standards if you have trouble sleeping at night. 

The Subject is mentioned quite a bit.  The way it works out, is that the subject is used as part of a trust anchor.  Think of each certificate like a fingerprint.  Each is supposed to be encrypted and different.  Occasionally you have twins, and the certificates are so similar that you need a way to tell them apart.  In the event of that situation the Subject is used to differentiate them.

So while you would think the subject is… well… the Subject of what you will use the certificate for, it is not.  I would generate a strong password and place it in the subject anytime I use a database certificate.  But let’s do a quick demo to show.

DEMO


First we will create a Master Key and a Database Certificate.

Create Master Key Encryption By Password='MasterKeyPass1'
GO
Create Certificate DatabaseCertificate With Subject='Dont Put Anything Importiant in the subject'
GO

Now let’s back them up to disk.  We’ll encrypt the certificate using a private key, and a strong password to encrypt the private key as well.

BACKUP CERTIFICATE DatabaseCertificate TO FILE ='C:\Encrypt\DatabaseCertificate.cer'
WITH PRIVATE KEY ( FILE ='C:\Encrypt\bradprivkey.key', ENCRYPTION BY PASSWORD ='$uper$ecretP@ssword')
GO

You should have 2 files from the backup the Database Certificate and the Private Key.  


Now let’s open up the Certificate in our handy hex editor.



And there it is!  Our subject sitting in plain text, not a huge security leak but for best practices I like to tell people not to put anything important in the Subject.

Thanks for stopping by Dear Reader!

Thanks,

Brad


Monday, August 13, 2012

Database Corruption, Transparent Data Encryption, and Trace Flag 5004


This one comes straight from the email bag.  A friend recently had a problem, they were placing TDE on a database and the encryption scan had stopped at state 2 percent_complete 0. I'm bouncing around the Charlotte NC airport facing some plane delays, and I thought what better time than to play around with a little database corruption. 

“So Balls”, you say, “What does TDE stuck in an encryption scan have to do with corruption.”

Great Question Dear Reader!  The default Page_Verify setting in SQL Server is Checksum.  This means when a page is read into memory and written back to disk a Checksum is calculated based off the pages contents.  When it is written back to disk, that Checksum is there.  When the page is read again the Checksum is used as a validation.  If the Checksum fails then it tosses an error reporting the page as a Suspect Page.

Think of this like going through the TSA Checkpoint, you’ve got your ticket and your identification.  If your ticket says ‘Serenity’, but your ID says ‘Zachary’ you will probably get flagged by the system as Suspect.  In both cases that’s where the probing begins. 

WE’VE GOT A PROBLEM

For this example I’m going to use a database that I’ve corrupted called CorruptAdventure taken from a corrupted version of AdventureWorksDW2008R2.  Horrible name for a database, it was just asking for corrupting.  We’ll start out assuming everything is fine.  The powers that be want TDE, Transparent Data Encryption, enabled on the database and we will do that.  First we’ll create our Master Key and a Database Certificate to use in the encryption.

/*
Create Master Key
and Certificate
*/
USE master
GO
Create Master Key Encryption By Password='MasterKeyPass1'
GO
Create Certificate DatabaseCertificate With Subject='Dont Put Anything Importiant in the subject'
GO

Now we’ll point to CorruptAdventure and create a Database Encryption Key and set encryption to on.  Transparent Data Encryption will read each page into memory.  If it doesn’t have a checksum one will get written.  Our page has a checksum, but it’s contents have been corrupted.  When SQL calculates a checksum to validate the current on, the page will get logged to the MSDB.dbo.Suspect_Pages table.

use CorruptAdventure
go
create database encryption key
with algorithm = aes_256
encryption by server certificate DatabaseCertificate
go
Alter Database CorruptAdventure
Set Encryption on
go

It looks like it is encrypting!

Whoa! We hit our error. 

Let’s query our Suspect_Pages table.  Just like I thought we’ve got our database ID and our page ID.  The error_type column is equal to 2, this means our page was flagged suspect during a Checksum operation. 
/*
It just stalled out
Why would this happen?

A page checksum occurs on all pages
whent the TDE scan
*/
select * from msdb.dbo.suspect_pages



Now let’s run DBCC CheckDB and verify if we really have something wrong with our database.

DBCC CHECKDB(CorruptAdventure) WITH NO_INFOMSGS

Msg 8928, Level 16, State 1, Line 1
Object ID 325576198, index ID 5, partition ID 72057594043498496, alloc unit ID 72057594044940288 (type In-row data): Page (1:3874) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 325576198, index ID 5, partition ID 72057594043498496, alloc unit ID 72057594044940288 (type In-row data), page (1:3874). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 325576198, index ID 5, partition ID 72057594043498496, alloc unit ID 72057594044940288 (type In-row data). Page (1:3874) was not seen in the scan although its parent (1:3888) and previous (1:3873) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 325576198, index ID 5, partition ID 72057594043498496, alloc unit ID 72057594044940288 (type In-row data). Page (1:3875) is missing a reference from previous page (1:3874). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'FactInternetSales' (object ID 325576198).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'CorruptAdventure'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptAdventure).


Just as I suspected corruption.   We got the Database ID and page number from Suspect_Pages and DBCC CHECKDB just verified that the page is indeed corrupt. Now we can find exactly what type of data is corrupted, which will determine our strategy for handling it.  We have the Object ID and Index ID for the DBCC CHECKDB Scan.

We can do a query against sys.indexes joined to sys.objects using the IndexID, 5, and ObjectId, 32576198, provided.  We will get the table name, index name, and index type.

select
     o.name as TableName
     ,i.name as IndexName
     ,i.type_desc
from
     sys.indexes i
     left join sys.objects o
     on i.object_id=o.object_id
where
     o.object_id=325576198
     and i.index_id=5



Our corruption is on a non-clustered index.  If you ever get corruption this is one of the easiest types to fix.  We drop our non-clustered index and re-create it, and it should fix everything.

USE CorruptAdventure
GO
DROP INDEX IX_FactInternetSales_OrderDateKey ON dbo.FactInternetSales
GO
CREATE NONCLUSTERED INDEX IX_FactInternetSales_OrderDateKey ON dbo.FactInternetSales
(OrderDateKey ASC)
GO

Now let’s Run DBCC CHECKDB to get a clean bill of health.

DBCC CHECKDB(CorruptAdventure) WITH NO_INFOMSGS

Excellent, looking at our TDE status it still hasn’t moved. 



The TDE encryption scan should have paused when the Checksum error occurred.  In case it didn’t you can manually pause the encryption scan and reset it with Trace Flag 5004.  Turning Trace Flag 5004 on will stop the encryption scan right where it is.  You then need to turn Trace Flag 5004 off so you can re-issue the encryption command and watch it commence.  You might not need to use Trace Flag 5004, but I like to play this one on the safe side.

DBCC TRACEON(5004)
GO
DBCC TRACEOFF(5004)
GO
ALTER DATABASE CorruptAdventure
SET ENCRYPTION ON

Let’s check our encryption status.



We are progressing again, and it looks like now we’ve completed!  Excellent, not only did we get our database encrypted but we were able to fix corruption that we were not previously aware of.  One last peek at our TDE scan and we see it is complete and our encryption_status is now 3, no longer stuck at 2.



Well my plane just arrived, so that’s all for now Dear Reader, as always Thanks for stopping by.

Thanks,

Brad

Wednesday, March 21, 2012

Spring DevConnections 2012



Hello Dear Reader, this week finds me in beautiful Miami FL.  Next week I’ll be in Vegas, for one night only.  Next week the SQL Server Conference schedule for 2012 kicks off, and how better to do it than in Las Vegas with DevConnections.  Connections could not come at a better time, with SQL 2012 RTM being available for MSDN download and available everywhere else April 1st, (yes I know April Fool’s day you can’t make this stuff up).

Joining the long list of experts speaking at DevConnections is ME!  I’m thrilled to be participating in the Launch Party for SQL Server 2012.  I was honored to be invited to present at SQL DevConnections.  For those of you not familiar with DevConnections it is a twice yearly conference that cover’s Visual Studio, ASP.NET, Sharepoint, SQL Server, Silverlight, HTML 5, Cloud, Exchange, and Windows technologies.

And it doesn’t just cover them, each technology stack is its own conference.  So literally there are 9 conferences for Microsoft Related technologies all happening in one place!   Under one roof!   Technogeeks of the World UNITE, I have found your mecca and it is called Vegas.

“So Balls,” you say “What will you be presenting on? When will it be?”

Excellent question Dear Reader and away we go.


WHAT ARE YOU PRESENTING
http://www.flickr.com/photos/skitteny/4279482815/ 


Since joining Pragmatic Works, see Pragmatic Works has Balls….SQLBalls, I have been very busy with client work, and this will mark my first presentation of the new year!  My subject will be Transparent Data Encryption in SQL 2012!  The When Dear Reader is next Thursday 3/29, 9:45 am.

I will be presenting on Transparent Data Encryption Inside and Out in SQL 2012.  Here is the abstract.

Security is a very important part of your job and in how data is utilized.  We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list.  Find out What it does and What it doesn’t do, How it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), What the X.509 Encryption Standard is and Why you should be careful of what you store and where, and other Advance Features as well as some tips on how to manage it.

This is a great subject and I’m kicking off the new year by walking through the process, talking about what TDE impacts and changes (not just in your database but in your Disaster Recovery Planning), and taking a quick peak at the internals of an MDF file.



WHY SHOULD I GO
http://www.flickr.com/photos/andrewdbui/5334073051/ 

DevConnections units so many technologies in the Windows stack, it brings out some of the best in the business on all fronts, and the people presenting are amazing.   Paul Randal (Blog|@PaulRandal) and Kimberly Tripp (Blog|@KimberlyLTripp) co-chair the conference, with the level of quality that Paul & Kimberly put into everything you know that this is going to be good.

The people presenting back that up you have Aarron Bertrand (Blog|@AaronBertrand), Steve Jones (Blog|@Way0utwest), Jonathan Kehayias (Blog|@SQLPoolboy), Brent Ozar (Blog|@BrentO), Mike Walsh (Blog|@mike_walsh), Glenn Berry(Blog|@GlennAlanBerry), Grant Fritchey(Blog|@GFritchey), and many many more, to see the full list click here.  When you get speakers this good you know that the content will be amazing and it is,click here for a list of sessions.

And as if that isn’t enough it’s VEGAS!

HOPE TO SEE YOU THERE

My time in Vegas will be brief, I’m a one night only, kind of show.  Right now I’m booked to the gills with client commitments so I will be flying in Wednesday night, and back out Thursday after the presentation.

So if you get a chance in passing feel free to yell out “HI Balls”, you’ll turn a few heads but hey it is Vegas, and I’ll make sure to come over and say Hi right back.

See you there!

Thanks,

Brad

Monday, February 27, 2012

I’m Going to SQL RALLY!




Hello Dear Reader, I’ve just received great news I’M HEADED TO SQL RALLY 2012! But I didn’t get here on my own, I have you to Thank.  And I would like to do just that.  Thank You to everyone who voted for me as part of the recent Community vote!  I really appreciate it.  It is always an honor to be picked to participate in a SQL event, but it means a lot when your peers vote you in.

I promise that you will not be let down, I’ve got not one, but TWO amazing sessions that made it through the voting.

“So Balls,” you say, “What are you presenting on?”

Great question Dear Reader, and away we go!


Transparent Data Encryption Inside and Out in SQL 2012

Security is a very important part of your job and in how data is utilized.  We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list.  Find out What it does and What it doesn’t do, How it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), What the X.509 Encryption Standard is and Why you should be careful of what you store and where, and other Advance Features as well as some tips on how to manage it.


I’ve had a lot of fun presenting on this topic in the past.  Transparent Data Encryption is a wonderful technology that we were able to start using in SQL 2008.  I was lucky very early on that as soon as I started working with 2008 I was working with TDE. 

When you use TDE there are some very important things to know and consider the first is what TDE does and doesn’t do, the second what physical changes actually occur within your database, and finally how you manage certificates and how they affect your recovery scenarios for your databases.

I’ve done a Lightening round version of this presentation for OPASS, the Orlando SQL Server User Group, my friends at Publix, and for SQL Saturday 79 South Florida this past year.  This year I start off the year with a Bang presenting on this topic at SQL Connections in Las Vegas, and now I’ll get to present on this at SQL Rally 2012 as well!


SQL Internals, Recovery Models, and Backups! OH MY!

The more you know about SQL Server the more you understand how it works.  SQL Server is a product we use every day, and most of us know the big concepts.  At the 10,000 foot view we know what  Databases, Tables, and Columns are.   But what makes up those Databases, Tables, and Columns.  What are Records, Pages, Extents, and Allocation Units?  What are Full, Simple, and Bulk-Logged Recovery?  What are the differences between  Full, Transaction Log, Differential, or Filegroup backups?  What is a Piecemeal Restore?  This is an introduction to these concepts using SQL 2012.  In this session you will learn about the internal Structure, Recovery Models, and Backups and be better prepared to for Future Learning and Managing SQL!


When I first put together this presentation it was meant to be a leap frog session. 

“So Balls,” you say “I’m not playing leap frog with you.”

No worries Dear Reader, I’m not playing a game, but what I mean by leap frog is I want you to leap ahead.  As I’ve studied SQL Server I’ve noticed that there are some fundamental concepts that you encounter over and over again.

The reason because they are all interrelated.  A.C.I.D., Transaction Isolation Levels, the internal components of SQL Server’s Relation & Storage Engine, B-Tree Structures, Pages, Allocation Units, Transaction Log management, Recovery Model’s, and Backups (that’s a mouth full OH MY!).  I’m not going to make you an expert, but I’m going to arm you with knowledge and concepts to allow you to go forth and be better prepared for future learning. 

But what we cover will apply directly to how you would choose the type of backups that are appropriate for the database systems that you manage.

THANK YOU

Once again Dear Reader and Dear SQL Community, I would just like to say Thank You.  I hope to see you and I hope to see you at SQL Rally 2012!

Thanks,

Brad

Wednesday, June 15, 2011

SQL Rally Q&A: Where is the SQL Engine does Compression Occur


This is another follow up on the Q & A’s that came about from SQL Rally, this question was asked by Jim Murphy (@SQLMurph | Blog).   Jim asked “Where in the SQL Engine does Compression Occur.”  Well Dear Reader the simple answer is in the Access Methods, but let’s dive a little deeper.

THIS IS YOUR BRAIN ON SQL



If you think of the SQL Server Engine as a brain, which has 2 hemispheres right and left, it has 2 parts the Relational Engine (right) and the Storage Engine (left). 

The Relation Engine receives a Query from the end user and it Parses it for syntax, Checks for a Query Plan, Retrieves it or Creates and Caches an execution plan, and finally it begins Executing the instructions to retrieve the Data.

This is where the Storage Engine comes into play.  The Relational Engine says here is what I want, and makes a Grocery List.  The Storage Engine takes that list and goes to the store to get all of the groceries. 

The Storage Engine receives the instructions of the Data that is required by the Relation Engine via the Access Methods.  The Access Methods will ask the Buffer Manager if this information is already in the Buffer Pool as part of the Data Cache.  If the Buffer Pool does not have the data in the Buffer Pool (ACTIVE MEMORY) then it is read off of Disk into the Buffer Pool.

As I mentioned earlier the Part of the Storage Engine that handles Compression is the Access Methods.


SO AMERICA WHY DOES THIS MATTER?



“So Balls,” You Say, “Why is this so important?”

Well Dear Reader it is because when the data is in the Buffer Pool (Active Memory) it is still in a compressed state.  So you are using the memory on your server more efficiently.  Not only is compression saving you space on disk, I/Os being read off of Disk, but the space occupied by the cached data pages is used more efficiently. 

A good example of why this is important is Transparent Data Encryption, TDE.  TDE is manage by the Buffer Pool.  That means that the data is unencrypted when it is read off of the disk and into the Buffer Pool Data Cache.  Because Page & Row Compression are managed by the Access Methods the Data is still compressed in Memory.

Another good example is when using backwards compatibility.  I've blogged about being able to use TDE on a SQL 2008 R2 Database using compatibility level 80 as well as a SQL 2008 R2 Database using Page & Row Compression using compatibility level 80.  The reason is that backwards compatibility changes the rules used by the Optimizer in the Relational Engine.

 The Storage Engine is not affected by compatibility level 80.  So knowing how SQL operates under the covers will show you how you should expect it to act, and help you know how to use the product to it's fullest extent.

Want to learn a little more about SQL Server Internals?


A great book on that is Professional SQL 2008 Internals and Troubleshooting, it is a wonderful read and there are more SQL top names on this book than you can shake a stick at.  I recommend having this book in your library.  It will help you on your way to discover more about how SQL works Internally, I know it helped me!

Thanks,

Brad

Thursday, April 28, 2011

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