Pages

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.

2 comments:

Not sure I'd see a subject as a security issue unless you were using the password as a subject. The subject is there to provide info about the cert, and as you mentioned, differentiate them if needed. I'd think about the differentiation part more than worrying about security of the subject.

About Me

Hi I'm Brad, I'm a Certified SQL Server Database Administrator. I've been in the IT business for over 10 years and worked as a DBA for the U.S. Army, Executive Office of the President, and Publix Supermarkets in the great state of Florida. And now I'm a Sr. Consultant for Pragmatic Works.