SQL Saturday 151 BI Pre-con: Stacia Misner

Hello Dear Reader, SQL Saturday 151 Orlando is picking up steam.  The Pre-Con’s have been named and they are fantastic.  SQL Saturday Orlando is always a big event, the schedule has been posted, and the planning is well underway.  First stop the BI Pre-Con Featuring Stacia Misner(@StaciaMisner|Blog) taking place at the beautiful Lake Mary Hyatt Place hotel.

“So Balls,” you say, “Who is Stacia and why should I attend?”

Great question Dear Reader, I work with a lot of really great people in the BI world, even though that area is not my forte, and everyone agrees Stacia is one of the TOP BI experts in the world.  She is one of the instructors for the Microsoft SSAS Maestro Program.  Stacia has been the author of over 12 different books on the subject of SQL Server.  

Her most recent is Introducing SQL Server 2012, available as a free PDF download click here to get it!  I first met Stacia at SQL Saturday #62 back in Tampa in 2011.  She had also written the book introducing SQL Server 2008 R2 and I had some questions about Master Data Services.  I was trying to figure out if Master Data Services in the 2008 R2 release was right for a project I was working on.  I asked her if I could get her advice and she was polite, candid, and very helpful.  Now it is your chance to get to meet Stacia.

A 360-Degree View of SQL Server 2012 Business Intelligence

One of the greatest things about SQL Saturdays is that the top SQL speakers and consultants will offer their training services for an unbelievable deal.  Stacia has taught BI Immersion Courses, Pre-Cons, and spoken at seminars that cost thousands of dollars to attend. You can attend her SQL Saturday session, and get one on one time with this expert, for just $99.   So now let’s look at the plan for the day.

In this session, we’ll take a holistic look at the BI features in the latest version of SQL Server by reviewing the architecture requirements, exploring the implications for existing BI applications, and introducing new capabilities that support the transformation of data into business insight. We'll start with data integration and management by reviewing the overhaul that Integration Services received in this release, how to formalize the data cleansing process by using the new Data Quality Services, and how master data management is improved with the updates to Master Data Services. Then we'll discuss the improvements to analytical capabilities by exploring updates to Analysis Services, including the new Tabular Model, and enhancements available in PowerPivot. Last, we'll cover the new presentation layer options available in Reporting Services and the new release of Power View. Of course, you’ll see demonstrations of the new features, but the primary purpose of this session is to give you a chance to ask lots of questions and to get a look “under the hood” to better understand what you’ll need to do to get these BI features up and running properly. You’ll also learn how to prepare your data environment to leverage these features and how best to manage the user experience.

If you are in a BI shop, or are looking to expand your career and get more in-depth in the BI field this is a great training opportunity.  Click here to sign up for the pre-con.  Click here to register for SQL Saturday 151 if you haven’t already.  I hope to see you there!  As always Thanks for stopping by!

Thanks,

Brad

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

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