SQL Server 2005: using symmetric keys to encrypt data

In SQL Server 2005, the recommended method for encrypting data is to use symmetric keys. In this post, I'd like to comment on three topics related to using symmetric keys:

1) basics of using a symmetric key
2) ways to restrict access to a symmetric key
3) how to prevent a symmetric key loss

So, let's tackle each of these:

1) Basics of using a symmetric key

A symmetric key can be created using the CREATE SYMMETRIC KEY statement. Information about symmetric keys is stored in the sys.symmetric_keys catalog. Encryption and decryption using a symmetric key is done through the EncryptByKey and DecryptByKey builtins; the latter also has two additional flavors, DecryptByKeyAutoCert and DecryptByKeyAutoAsymKey, which I'll address later in this post. Before using a symmetric key, however, the key must be opened - this is done using the OPEN SYMMETRIC KEY statement. The opening of the key is the operation through which the key is decrypted and made ready for use. A key that is opened will only be available in the current connection, and this availability will last until either the key is explicitly closed using a statement like CLOSE SYMMETRIC KEY or until the connection will end. Keys that are opened in the current session can be listed by executing:

select * from sys.openkeys;

Caveat: There is currently a defect (#409522) in sys.openkeys that can break the DMV if it is used in a query that searches for a specific key in the catalog. This bug will occur whenever there are multiple keys opened and the one that is specifically searched is not the last entry in the catalog. For example, the defect can be hit by executing a query like:
select * from sys.openkeys where key_name = 'key_DataEncryption';
The defect behavior is that such a search can leave the catalog in a bad state, so that all subsequent queries over the catalog will return no results. The workaround is to avoid such queries if possible, or to select the contents of the DMV into a temporary table and run such queries on the temporary table, or to just keep track of the opened keys through some alternative method.

UPDATE [2006/08/16]: This defect was fixed in Service Pack 1. I posted this information earlier in a comment below, but I'm also editing the original post to make this point more clear.

CLOSE SYMMETRIC KEY will close a specific key. If we want to close all currently opened keys, we can execute CLOSE ALL SYMMETRIC KEYS rather than closing each key individually.

If you select from sys.symmetric_keys, you will notice a key_guid attribute. This is the key identifier and it is always prepended to encrypted data, such that during the decryption, the key does not need to be specified - the key_guid value will be used to identify the appropriate key and the decryption builtin will look to see if that key is already opened, and if it is, it will be used to decrypt the data. This identifier is required by the encryption builtin and it can be retrieved from the symmetric key name using the key_guid builtin - quite easy to remember. See my previous posts on the subject of column encryption for examples of how these builtins are used (ex.1, ex.2). Also, for information on some other catalogs relevant to encryption, see this post.

2) Ways to restrict access to a symmetric key

There are two restrictions on using a symmetric key: a user that attempts to use a symmetric key must have some permission on the key and must not be denied the VIEW permission (see description of OPEN SYMMETRIC KEY in BOL), and the user must additionaly have the capability of decrypting the key.

Because of the many ways a symmetric key can be encrypted, there are many ways you can protect a key. Let's examine these possibilities:

(a) the symmetric key can be encrypted using a certificate's public key, so the certificate's private key is necessary for decryption. The certificate's private key in turn can be protected either by a password or by using a database master key. The database master key (DbMK) can also be encrypted by the service master key (SMK) or only by a password. And the SMK is protected using DPAPI. This may sound overly complicated, but the presence of a key hierarchy allows for changes of a key with minimal changes to the existing encryptions done with that key. Also, a key chain rooted at the SMK allows the key to be manipulated without the user having to specify any password, but the drawback is that any person with access to the key and to the SMK (by default any sysadmin) can decrypt the key. A key rooted at a DbMK (this is the case of the DbMK encrypted only by password) restricts the use of the key to anyone who has permissions on the key and on the DbMK (by default any db_owner with knowledge of the DbMK password). A key rooted at a password restricts the key usage to users with knowledge of the password. These levels of restriction are also described in a recent post on the yukondoit blog.

Related to this, the DecryptByKeyAutoCert builtin is designed to allow the decryption of a key encrypted with a certificate by using a single builtin call. This builtin will automatically open the symmetric key, if it is not already opened. If the symmetric key was not opened already, then it will be closed before the builtin returns.

(b) the symmetric key can be encrypted using an asymmetric key's public key. This is similar to (a).

(c) the symmetric key can be encrypted using a password.

(d) the symmetric key can be encrypted using another symmetric key. While this opens the possibility of a very long chain of symmetric key encryptions, in practice I can't think of a reason to create a chain longer than two (symmetric key encrypted by symmetric key encrypted by one of the previous mechanisms: (a), (b), or (c)).

Also, note that a symmetric key can have several encryptions of any kind, so we can actually have all these ways of accessing a single key. In practice, I would not want to abuse these choices unless I'm looking for a headache, so I would instead want to pick the method that is most appropriate to the type of protection I want to achieve

So which of these is the best choice? Unfortunately, the answer to this is: it depends. If there would be a clear best choice, there would not be much sense in having all these options. So let me give some reasons why I would want to pick a method over another:

If I would want to rely on the key hierarchy so that I don't have to enter a password when opening the key and I don't mind that a sysadmin can access my data, I would use (a) or (b). Of course, by having the certificates protected by passwords, I would address the concern that a sysadmin can use them, but this would make the solution more similar to (c) and (d).

If I would want to protect data so that only I can access it, I would use (c) - this is really the simplest method.

If I would want several users to be able to access a key K, I could have several choices. If I don't mind privileged users having access to the key besides my group of users, I could have the key encrypted by a certificate (or asymmetric key) for each user, with each user having access to a certificate. If I would want to restrict access only to my group of users, I could have each of them encrypt the key with his password - option (c), but a better solution is to have them encrypt the key K with a set of keys Ku, one for each user - option (d), which is better for revocation purposes, because in the case of using (c), to revoke a user's access to K, I would need to know the user's password, but for (d), to revoke access, I can just drop the encryption made to K using the corresponding key Ku.

3) How to prevent a symmetric key loss

Because there is no direct way to individually backup and restore a symmetric key, what can we do to protect these keys? One solution is to do frequent database backups. Because the keys are stored in the database, they will be saved with the database. There is an additional solution, however, which involves deriving a symmetric key from a passphrase. The same key can always be recreated as long as the same passphrase is used. To create a key derived from a passphrase, we can use the KEY_SOURCE clause of CREATE SYMMETRIC KEY. While KEY_SOURCE will allow us to regenerate the same key, to be able to decrypt data encrypted by a previous incarnation of the key, we should also generate the key with the same key_guid value; for this, we must use the IDENTITY_VALUE clause. Together, the KEY_SOURCE and IDENTITY_VALUE clauses provide a way to recreate the same key with the same key_guid identifier.

Comments (16)

  1. SteveJC says:


    Section 3, I am still confused, sorry.

    When you say Key Loss, is this the same as forgetting the key or the key has become corrupt and you need to rerun the "create symmetric key" and regenerate the same key?

    Also if a symmetric key is encrypted by a password and the password is forgotton, does this mean we can never decrypt the data?


  2. For section 3, it would cover both forgetting the password that protects a key or having the key corrupted somehow, as both of these represent a key loss. Dropping and recreating the symmetric key with the same algorithm, key_source, and identity_value used for its original creation will restore the key.

    If the password is forgotten, then for all practical purposes the key becomes unusable, unless it was protected by other additional means. Remember that a symmetric key can be encrypted using several keys or passwords. But if the key has only one protection by a password and the password is lost, then the key becomes unusable.

  3. Just an update: the defect I mentioned in this post (#409522) was fixed in Service Pack 1.

  4. MS says:

    As dbo, I am attempting to allow encrypt and decrypt permissions for a DAUser.  Is it possible with the DbMK encrypted only by password (and not by SMK – want to allow dbo control only).

    As DAUser, I understand how the DecryptByKeyAutoCert() function can be used without the symmetric key or DbMK having to be first opened.  However, when I remove encryption by SMK I am warned that the DbMK has to be first opened.  Is this necessarily a permissions thing?

    For example:
    create master key encryption by password = ‘Pufd&s@))%’;


    create certificate cert_sk_DAUser
    authorization DAUser
    start_date = ‘20060626’;

    create symmetric key sk_Person_InternalEmailAddress with algorithm = triple_des encryption
    by certificate cert_sk_DAUser;

    grant view definition on symmetric key::sk_Person_InternalEmailAddress to DAUser;

    close all symmetric keys;

    Executing as DAUser, I would like to now run something along these lines:

    select convert( varchar, DecryptByKeyAutoCert( cert_id(‘cert_sk_DAUser’), NULL, Person.EmailAddress ))

    Where the EmailAddress is encrypted by the new key.

  5. DecryptByKeyAutoCert cannot open the database master key automatically. If the DbMK is encrypted by password only, then it can only be opened manually using the open master key statement.

    For your scenario, however, you do not need to use the database master key. You can just encrypt the certificate using a password, rather than using the DbMK, and then you need to pass that password to DecryptByKeyAutoCert as in the script below:

    — create test database

    create database test;

    use test;

    — I had to create this user on my machine

    create user DAUser without login;

    create certificate cert_sk_DAUser

    authorization DAUser

    encryption by password = ‘Pufd&s@))%’

    with subject = ‘DAUser key encryption certificate’, start_date = ‘20060626’;

    create symmetric key sk_Person_InternalEmailAddress

    with algorithm = triple_des

    encryption by certificate cert_sk_DAUser;

    grant view definition on symmetric key::sk_Person_InternalEmailAddress to DAUser;

    open symmetric key sk_Person_InternalEmailAddress

    decryption by certificate cert_sk_DAUser with password = ‘Pufd&s@))%’;

    select * from sys.openkeys;

    select encryptbykey(key_guid(‘sk_Person_InternalEmailAddress’), ‘Encryption test’);

    — result of encryption was:

    — 0x003D06F71E394B4D915F7BF396F0EFEE01000000824413E2978964E2016181618FC946B0158A1A8FBC557ED2E1757CCB49C857BC

    close all symmetric keys;

    execute as user = ‘DAUser’;

    select convert(varchar, DecryptByKeyAutoCert(cert_id(‘cert_sk_DAUser’), N’Pufd&s@))%’,



    — cleanup

    use master;

    drop database test;

  6. MS says:

    Thanks for your help.  I should have made it clearer, but I want to avoid any SQL running under the DAUser  having passwords hardcoded.   I think I’ll definitely have to maintain DMK encryption with  the SMK.



  7. The password doesn’t have to be hardcoded in the application, it can be passed to it. But if you don’t want the user to handle passwords, then you have to rely on the SMK encryption, so that the system can automatically decrypt.

  8. David Fabrycky says:

    Please clarify/verify Symmetric Keys – Encryption by Password especially

    with regard to the ASSUMPTION below.

    The following scenario is included for convienience and elaboration.

    1.   SQL Server 2005 offers a Password Encrypted Symmetric Key.

         a)    SQL Server 2005 uses the (Strong) Password to derive a

                Triple-DES Key with which to encrypt the Symmetric Key.

    2.   The Triple-DES Key is derived and not stored (ASSUMPTION) on

         SQL Server nor the supporing devices e.g. disk.

    3.   Upon attempting to open the Symmetric Key the Users must provide

         the Password in order for SQL Server 2005 to (re-)derive the Triple-DES

         Key with which to decrypt the Symmetric Key for further use.

    Thank you in advance.

  9. Your assumptions are correct.

  10. Regan Wick says:

    Thank you for your insightful explanations. I have a question.

    Even if semetric keys are saved in a back up and therefore available upon restore, if the semetric key was encryptic using a certificate, that may not be available -as if the restore is to a different box. It is possible to recover certificates but you must first drop the cert which requires dropping the semetric keys.

    Given all of this is it best just to follow your "3) How to prevent a symmetric key loss" above and use key_guid and IDENTITY_VALUE and skip the asym cert.

    Bottom line, I am look for a way to back up a DB and restore to a different box, reapply encryption from back ups and have the data accessible.

    Any help much appreciated.

  11. Certificates are also stored in the database, so they should be available with the symmetric keys. You don’t loose the certificates if you move the database to another server. The only thing you may need to do after restoring a database on a different server, is to restore the SMK encryption of the DbMK. For this, you need to execute the following statements in the database after you restored it:


    Other than this, you don’t need to do anything special to be able to work with the encrypted data like you worked on the original server.

  12. Regan Wick says:

    Many thanks! You have helped greatly.

    You are very generous with your time and explanations. May someone turn you a good favor also….

  13. Jeffrey Welch says:

    I’m new to SQL 2005 and i’m learning about the encryption in SQL 2005. I have a database I created and I have created my master Key and I have encrypted it in AES. Are there any pitfalls I should lookout for when using the encryption in SQL 2005? Or are you aware of any issues far as user and data problems. thanks for any help you can provide me.

  14. Pitfalls can arise from not using encryption properly, so make sure you understand how encryption works in SQL Server 2005. Post questions that you have on the SQL Server security forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1.

  15. Twiggy says:

    How does all of this effect Log shipping or database mirroring?

    I’m about to setup this to protect my CC numbers. I also have log shipping which one day will be replaced with database mirroring.

    Regardless shouldn’t everyone use option 3? You can still encrypt it using a password or cert afterwords. My entire company is on the line if I loose access to it, so being able to recreate the key guaranteed is very important. Using a very strong passphase that no one could possibly remember and then storing it in a secure location away from IT seems to be the solution.

    I like the cetificate option because then you don’t have to worry about procs and other queries having to “know” a password. But it seems this would be the only way if you wanted to use database mirrioing which would switch your servers to the client automatically. Would it be possible to have the cert restored correctly into a database that was in standby? It doesn’t seem so.

  16. For log shipping and database mirroring, there is one caveat that needs to be addressed, in the case where the DbMK has an encryption using the SMK.

    Here’s the problem. I’ll keep the description generic by saying that we’re trying to keep a database in sync on two servers, A and B. Servers A and B will have different SMKs. The database residing on A will have a DbMK encrypted by the SMK of server A. Replicating the database on server B will copy this encryption, but then the DbMK cannot be decrypted automatically on server B, because the SMK of server B is different. To handle this scenario, we need to use the sp_control_dbmasterkey_password procedure (http://msdn2.microsoft.com/en-us/library/ms182754.aspx) to specify one of the passwords that encrypts the DbMK. We need to use this procedure on both servers A and B. As long as the passwords specified this way continue to be valid passwords that protect the DbMK, SQL Server will be able to decrypt the DbMK using these passwords, allowing access to the encrypted data, as usual.

    If you have backups of the database containing the keys, then you cannot lose those keys. This is why I cannot say that everyone should use option 3.

    I don’t understand your last question about the restoration of the certificate. What is the scenario you had in mind?

Skip to main content