Issues With SQL 2005 Encryption


  •   Here are the most common issues that probably you should be aware of before using encryption:

    • Encryption is not deterministic. This affects the ability to index encrypted data. I wrote an article describing this problem in detail as well as a few suggestions that may help you to solve this problem, you can find this article in my blog:
    • Performance. Encryption/decryption will consume some extra cycles. Unfortunately I don’t have any numbers to share here, but a suggestion would be to try to minimize the encryption/decryption operations to the minimum.
    • Key backup. In SQL Server 2005 there is no way to backup individual symmetric keys, you need to backup the whole database. As an alternative, you could use a passphrase-derived key in order to be able to recover the key without the need of recovering the whole database.
    • Symmetric keys are not schema-binded. Encryption is based on builtins, and the tables/columns are unaware of encryption; it is possible to drop a key by mistake. Avoid dropping any symmetric keys unless you are absolutely sure it is not needed anymore, and I strongly recommend to have a backup of your database (see point above) or using a passphrase-derived key to prevent data loss.
    • The space needed to store an encrypted column is larger than for the plain text. I wrote an article that explains this issue in detail in

       I strongly recommend to read other encryption related articles in the following blogs:

    * Laurentiu Cristofor's blog (

    * Raul Garcia's blog(


      I hope this information will be useful, please let us know if you have any further questions.



     -Raul Garcia


      SQL Server Engine

    3 ตุลาคม 2549 18:29
  •   That is really strange. The key ring is session based, may it be possible that you are using 2 different sessions to open the key and to alter it? If not, I will appreciate if you can give us some more information to try to repro this problem (what client you are using, the script you are trying to run, etc.)


      BTW. You can run SELECT * FROM sys.openkeys to see the keys opened in the key ring.


      Thanks a lot,

      -Raul Garcia


      SQL Server Engine

    4 ตุลาคม 2549 17:58