How to backup asymmetric key in SQL 2005

Question

How to backup asymmetric key in SQL 2005 created in the following way so it can be copied to another server ? Also can you copy it to the other server after backing it up.

CREATE ASYMMETRIC KEY ccnumber WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'password';

Answer 1

Did you have a look on

BACKUP CERTIFICATE (Transact-SQL) ?

 

HTH, jens Suessmeyer.

Answer 2

You should use certificates instead of asymmetric keys, if you need the ability to backup the keys. Asymmetric keys cannot be backed up individually, they can only be backed up with the database.

Thanks
Laurentiu

Answer 3

I figured out a way to grant permissions to the web user to decrypt data encrypted using asymmetric key. I can do that using alter authorisation method.

I think using asymmetric is the most secure way of doing encryption because the password is not in the database so even someone gets to the database they cannot do anything without the password but if I use certificates they can decrypt.

Let me know if I am right or wrong.

Answer 4

ALTER AUTHORISATION will change the owner of the key. That will work, but if you don't want to change the key owner, you should just grant CONTROL on the key.

You can also encrypt symmetric keys using just a password, so using asymmetric keys doesn't confer an advantage from this point of view. Certificate private keys can also be encrypted by password. The password encryption is not a property exclusive for asymmetric keys. You should use symmetric keys for encrypting data.

Thanks
Laurentiu

Answer 5

Looks like we mixed posts between two threads. To avoid confusion, please open a new thread if you have additional questions on using symmetric and asymmetric keys.

Thanks
Laurentiu

Answer 6

What is the exact complete syntax to grant control on a asymmetric key?
Also can you please tell why I should I use symmetric keys to encrypt data and not asymmetric?

Answer 7

Asymmetric key encryption is much slower than symmetric encryption.

Also, you can encrypt larger pieces of data using symmetric keys than by using asymmetric keys (but you could use the functions described here if you really wanted to encrypt larger pieces of data: http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx).

Syntax is:

GRANT CONTROL ON ASYMMETRIC KEY::akey TO alice;

There is a certificate example for this in the following demo (search for "grant control"):

http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx

Thanks
Laurentiu

Answer 8

Thanks for the syntax to grant control.

I am just encrypting 16 digit credit card numbers and want to get strongest encryption, so I think asymmetric will be better. Right?

Answer 9

No, asymmetric key algorithms are not necessarily stronger than symmetric key ones. Also, with symmetric keys you can setup better key access schemes than you can with asymmetric keys. See http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx, for an example.

Thanks
Laurentiu