SQL Server 2005: certificates vs asymmetric keys

If you have ever wondered what is the difference between certificates and asymmetric keys in SQL Server 2005, then this post is hopefully going to answer this question.

First, let me make one important point clear: both certificates and asymmetric keys are encapsulating RSA keys, hence they are both containers for asymmetric key encryption keys. Hence, there is no difference between these as far as the cryptographic algorithm is concerned, and no difference in strength either, given the same key length.

So, why do we have both? Historically, support for certificates was introduced first, to allow the import of cryptographic keys from X.509 certificate files and the export of keys to such files. Asymmetric keys were introduced later to support the import of cryptographic keys from strong name files or assemblies. The syntax was kept separate because some of the existing creation options for certificates had no meaning for strong names (expiry date, for example). Thus, asymmetric keys were born next to certificates, as a more generic key container and having a more generic name as well.

So, which of these should we use? Currently, I prefer certificates, because of the option of exporting the keys to .CER files. Functionally, however, there is no significant difference between what you can do with a certificate and what you can do with an asymmetric key. The main factor that can force a decision here is if the keys are generated outside the server in either certificate format or strong name format; in this case, you would probably want to use the entity that allows the easier key import.

Finally, I would like to mention the existence of tools for creating certificates (makecert) and strong name files (sn); these tools offer a richer amount of flexibility in the key generation process than the SQL Server syntax. You can use such tools, for example, to create RSA keys with more exotic key lengths, then import them into SQL Server.

Published 13 March 06 06:45 by lcris

Comments

# Noel Fouts said on March 16, 2006 12:04 PM:
How does SQL Server handle the expiry date of the certificate?  Is the certificate invalid if it has expired?  Can we still decrypt the data we have encrypted with it?  My guess is we just add a new certificate to the key with the ENCRYPTION BY syntax.   I tried altering the certificate's expiry_date but it doesn't appear possible.
# Jared said on March 28, 2006 8:10 PM:
Thanks for the explanation!
# lcris said on March 29, 2006 1:38 PM:
Sorry for the late reply. There seems to be a bug in the blog management site and new comments were not reported in the aggregate view. I only discovered them today.

The expiration date of certificates is stored in metadata, but currently it is not used by the encryption features. You'll be able to encrypt and decrypt using the certificate past its expiration date. Service Broker, however, enforces the expiration date. If you need more info about the Service Broker use of certificates, you can ask Remus at: http://blogs.msdn.com/remusrusanu/.
# Boaz said on June 20, 2006 8:57 AM:
Hi,

I want to change the Cerificate used to encrypt a symmetric key every year.
I use the Alter symmetric key .... Add encryption by
to add encryption by a new certificate and then drop the old certificate. given that I need the new certificate name to open the symmetric key i see two options:
1) changing the code references to the symmetric key.
2) dynamicly retriving the name of the certificate used to encrypt the key.

Both options i dont like.

Is there an option to renew / regenerate a certificate or the second option mentioned above is the way to go?

Boaz

# lcris said on June 20, 2006 2:31 PM:
You can take the solution that you proposed one step further: you can add an encryption by a temporary new certificate, drop the old encryption and the old certificate, then create a new certificate with the old name, add an encryption by it, and finally drop the encryption made with the temporary certificate and drop the temporary certificate as well. Here's a small script going through these steps:

-- create a database for the test
create database test
use test

-- create master key
create master key encryption by password = 'Avsp;otNOcfo'

-- create a certificate and a key encrypted by it
create certificate certsk with subject = 'certificate protecting skey - version 1'
create symmetric key skey with algorithm = triple_des encryption by certificate certsk

-- open the key so that we renew the certificate encrypting it
open symmetric key skey decryption by certificate certsk
select * from sys.openkeys

-- create a temporary certificate and encrypt the key with it
create certificate certtmp with subject = 'temporary certificate for protecting skey while we renew certsk'
alter symmetric key skey add encryption by certificate certtmp

-- drop the old certificate encryption and the certificate itself
alter symmetric key skey drop encryption by certificate certsk
drop certificate certsk

-- renew the certificate
create certificate certsk with subject = 'certificate protecting skey - version 2'
alter symmetric key skey add encryption by certificate certsk

-- remove the temporary certificate
alter symmetric key skey drop encryption by certificate certtmp
drop certificate certtmp

-- close opened keys
close all symmetric keys

-- cleanup
use master
drop database test
# Boaz said on June 21, 2006 5:34 AM:
Hi,

First of all thanks for the quick reply.

As for your segestion. we actually thought of this option but it means that while the temp certificate is the only certificate in effect, stored procedures referencing the original certificate will fail.
Given that the change will happen only once a year this could be acceptible but I would prefer a renew process with no down time at all.

Boaz
# lcris said on June 21, 2006 4:21 PM:
For no downtime, for now, you would have to go with something like solution (2), so the certificate name is not hardcoded in the places that access the key.

For the future, we'll look at providing easier methods of renewing keys in SQL Server.

Thank you for your feedback.
# Boaz said on June 22, 2006 5:42 AM:
Hi Laurentiu,

What do you think about this solution to prevent down time during the certificate renew process done acording to your segestion:

The key is protected by two certificates, the actual certificate we want to use and a backup certificate which should only be used when the certificate does not exist during the renew process.

the code used to open the symmetric key looks like this:

BEGIN TRY
   OPEN SYMMETRIC KEY EKey DECRYPTION BY CERTIFICATE ECert
END TRY
BEGIN CATCH
   IF Error_Number() = 15151 --Handle Certificate not found
     BEGIN
        OPEN SYMMETRIC KEY EKey DECRYPTION BY CERTIFICATE ECertBackUP
     END
   ELSE
     BEGIN -- Hadle error different than certificate not found
    END
END CATCH

The ECert and ECertBackUP will be renewed seperatly.

This avoids dynamic code and ensures 0 down time

Boaz
# lcris said on June 22, 2006 2:03 PM:
If your application can be changed to open the key this way, then this will work very well.

One caveat is that if your application calls DecryptByKeyAutoCert with ECert, those calls won't work while you are renewing the certificate. But if you only use the certificate in this piece of code that opens the key, then you will have no downtime during its renewal.
# Boaz said on June 23, 2006 4:51 PM:
I actualy replaced a call to DecryptByKeyAutoCert with the code above to enable the proposed solution.
It looks like we will go with this solution.

Laurentiu, Thank you very much for your time and effort. (and great response time).
you run one of the most interesting and helpfull blogs around.

tanks again

Boaz
# lcris said on June 23, 2006 8:23 PM:
Thanks. I am glad I could help.
Anonymous comments are disabled

Search

This Blog

These posts are provided "AS IS" with no warranties, and confer no rights.

Syndication

Page view tracker