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.

Comments (11)

  1. Noel Fouts says:

    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.

  2. Jared says:

    Thanks for the explanation!

  3. 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/.

  4. Boaz says:


    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?


  5. 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

  6. Boaz says:


    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.


  7. 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.

  8. Boaz says:

    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:

       IF Error_Number() = 15151 –Handle Certificate not found
         BEGIN — Hadle error different than certificate not found

    The ECert and ECertBackUP will be renewed seperatly.

    This avoids dynamic code and ensures 0 down time


  9. 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.

  10. Boaz says:

    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


  11. Thanks. I am glad I could help.