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
