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.
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.
Other posts:
