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.