SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Asymmetric Keys in SQL Server

I've been tech editing an encryption book for SQL Server. It's long overdue, and I think it will be a nice addition to any DBA's library. It also addresses a complicated subject that Books Online has inadequately covered. In fact, they've done an incredibly poor job with just documenting encryption, much less trying to help explain how to use it.

If you're not familiar with asymmetric keys, they are a set of keys that are paired. One is used to encrypt data and the other is used to decrypt data. Often these are referred to as the public key and private key. The public key you disclose to other people and keep the private one, well, private. If someone encrypts data with your public key, only you can decrypt it with the private key. Not even the person that encrypted the data and decrypt it!

The reverse happens when you encrypt something with your private key. You can't decrypt it with that key, though you can decrypt it with your public key, which presumably you have access to. However the fact that your public key decrypts something proves you encrypted it, which is how a digital signature works.

SQL Server 2008 supports two types of asymmetric keys: standard asymmetric keys, and certificates. In case you were not aware, those certificates that you buy for your SSL web sites, or for other security functions, are asymmetric keys. The difference is that certificates have some other meta data, like expiration dates, that make them very handy.

One downside of asymmetric encryption is that it's processor intensive, and it takes time. In fact, it's much, much longer to encrypt (or decrypt) data with an asymmetric key, so they aren't supposed to be used to encrypt data.

Instead you use them to encrypt symmetric keys, and then use those keys for encrypting/decrypting your data.

This is an interesting book, being written by MVP Michael Coles, and I'm looking forward to digging in further.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.