THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Michael Coles: Sergeant SQL

SQL Server development, news and information from the front lines

No Backup For Asymmetric Keys

The encryption features in SQL Server 2005 and 2008 provide the ability to create asymmetric encryption key pairs (RSA public/private key pairs) using the T-SQL CREATE ASYMMETRIC KEY statement. One feature that was left out, however, is the ability to backup and restore asymmetric key pairs generated on the server. Once an asymmetric key pair has been created there's no way to export it out of the server. Unlike symmetric keys, you can't create a duplicate asymmetric key with T-SQL statement options. This can be a serious issue for your disaster recovery (DR) program - if a server goes down you'll never regenerate that same asymmetric key again.

What you can do, however, is import asymmetric keys that were generated by an external source. You can import asymmetric key pairs from strong name key (SNK) files (and public keys only from executable files and assemblies). As long as you import your asymmetric key pairs from SNK files, you can always back up those files to a secure location to handle your DR requirements.

Alternatively you can use certificates (via the CREATE CERTIFICATE statement), which contain an asymmetric key pair and some additional metadata (certificate name, subject, expiration date, etc.) SQL Server does provide the necessary facilities to backup and restore certificates.

Basically if you need to use asymmetric encryption on SQL Server, my recommendation is to (1) generate your asymmetric keys elsewhere and import them into SQL Server, or (2) use certificates instead of asymmetric keys.

So the question is just sort of hanging there -- what purpose does it server to create an asymmetric key pair that you can never export or back up? The best answer I've come up with so far is that you can use these randomly-generated non-exportable asymmetric key pairs for testing, when you don't want to provide your developers with your real production asymmetric keys. This answer is a little unsatisfying, so if anyone out there has a better answer, hit me up!

Published Friday, July 10, 2009 12:32 PM by Mike C



Tim Rowan said:

Asymmetric keys are best used to protect other keys and signing objects. Not the best option in SQL Server for encrypting data. Asymmetric encryption is much slower than symmetric encryption, will generate a larger result and it's data compression is also limited.

Here's some more background...

July 15, 2009 3:15 PM

Mike C said:

Very true Tim.  Keep in mind that if you install an asymmetric key on SQL Server to protect your symmetric keys you might want to be able to restore it later as part of your DR program.

July 23, 2009 9:59 PM
New Comments to this post are disabled

This Blog



Privacy Statement