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 DatabaseCertificateWith 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 DatabaseCertificateTO 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
