I've been tech editing a book about SQL Server encryption and this series of blog posts looks at some interesting things I've found when going through the book.
We're DBAs, right? We're concerned with data, with stability, and perhaps most importantly, backing up and restoring our data. We expect to be able to do this, and at a fairly granular level. I can back up a table or a view (through bcp), a stored procedure (scripting), a file group, or a database.
As I was checking facts in the encryption book I stumbled upon this caveat. You can't back up an asymmetric key!
There's not even a backup asymmetric key command in the T-SQL reference!
I was stunned and looked all around to try and figure this out. After all, I can create an asymmetric key from a file. You use some other program to create the key files, like sn.exe from the Visual Studio resource kit, but you apparently can't get this key back out of SQL Server once it's in there.
It is in a database backup and restore, so that's good, but if I wanted to use this key in another database, or perhaps more importantly, I wanted to put the public key in one database, say an order entry system where I take credit cards in and encrypt them with this key. I don't have the private key, but I move that key to a second system, which the order entry people don't have access to. I then transfer the encrypted data between systems and can decrypt it with the private key on the second system.
If I've created my asymmetric keys in SQL Server, I can't easily do this. Can't do it for DR if I need to get data decrypted, just have to be extra sure I have a good database backup and I can use it to decrypt the data.
There's a backup certificate command, a backup master key command, and even a backup service master key command (though strangely no create service master key command), but no backup asymmetric key command.
I think this is an oversight in SQL Server encryption (as is the lack of CREATE SERVICE MASTER KEY) and ought to be corrected. Hopefully it will in the future. In the meantime, I would not recommend you creating asymmetric keys in SQL Server. Instead, create them in Visual Studio and then protect the files.
Other posts:
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.
