Monday, August 20, 2012

Please VOTE for Me: PASS Summit 2012 Lightening Talks
Hello Dear Reader the PASS Summit is approaching and with that the program committee sent out a request for Lightening Talk Submissions.  Last year I was able to give a lightening talk, 24 a 5 minute Horror Story, about one of my worst on call shifts ever that unfolded during a 24 straight hours.  It involved the wrong RAID Drive being pulled, having to rebuild transaction logs, Master going nuclear and having to rebuild the system tables and restore from backup, and finishing off with a little DBA Prayer called “Please God let DBCC CHECKDB run clean so I can get to sleep….”.  It was fun and I tried to do it with as much humor as I could given the situation.

This year the Lightening Talks have been extended to 10 minutes, and I’ve submitted another that is now up for community vote. 

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

A very important topic Dear Reader and it is all about how to be a better DBA.  We all will go to the Summit and spend hundreds and in most cases thousands of dollars to attend.  What about the time in between?  

Once a year we have the largest get together in the World of SQL Server professionals.  After you go home how do you keep up with it?  Knowing where to look is the first step.  There are many many organizations that work tirelessly to keep the spirit of the Summit alive until we meet again, and you can get it without breaking the budget.  My topic is Get Top Notch Training for Free or Next to Nothing.


Top Notch, You've said it all.

  The greatest thing about Microsoft SQL Server is the SQL Server Community.  I would use it as a major selling point if I were a Microsoft Rep.  

Save thousands of dollars, yep.  Get features included with Enterprise Edition that cost hundreds of thousands of dollars with other vendors, check.  Has a community of millions of users who bust their butt’s regularly to give free training, documentation, assistance, and put on over 100 free training clinics in 2012 alone, Check.   

I admit I am biased here.  Most technologies have a gathering and professionals that go out of their way to help others.  You would be hard pressed to find one as grand in scale and scope as the SQL Server Community.  Without further ado here’s my Abstract:

The greatest thing about SQL Server is its Community. This is always spotlighted at the Summit, but throughout the year there is Free Training offered by Top SQL Minds, MVP's, and MCM's alike. Learn about Webinars, User Group Meetings, and SQL Saturdays and how to keep your SQL Learning going all year long.

There’s a lot to be gained by going to the big conferences, but if you’re in a shop where the budget isn’t there you don’t have to miss out.  My company Pragmatic Works has free training on the T’s (Tuesday and Thursday’s), SQL Skills has their Insider video’s, The Brent Ozar PLF has weekly webinars, Idera has the Ace program, and you name it (and sorry to anyone I left out) we've got it!

Not to mention the PASS Virtual ChaptersDBA, DBA Fundamentals, Performance, PowerShell, Big Data, Business Intelligence, and more!  Want a preview of the great content you will get at the PASS Summit 2012 look no further than the 24 Hours of PASS, once again completely free.

Want to be able to reach people in person and network?  Maybe you should attend a SQL Saturday, check out SQL Saturday 151coming up in Orlando Saturday September 29th, where the same people and many of the same presentations given around the globe are brought to the local community.  

Want community more than once or twice a year?  Check out your Local SQL Server User Group to the uninitiated), where you meet the DBA’s that make up your local community, once again absolutely free.

Many conferences will give you a chance to get training that may not make it out to any of these channels (the Microsoft PSS team the CAT team and other Microsoft guru's), and I would argue that they are still very important and valuable reasons to attend.

However, knowing where to look when those conferences are gone and just a memory and notes on a page is priceless.  So this will be my presentation.  It will be chock full of links to resources, how to find information, what sites to go through (I still haven't mentioned forums!).  Better yet being a lightening talk we'll have some people in the room that may be able to contribute more as well!



Thursday, August 16, 2012

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!



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.


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.


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.


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

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

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')

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!