Recently my video titled "Create an Asymmetric Key in SQL Server 2005 / 2008" was featured on JumpstartTV. A superb feature of JumpstartTV is that the viewers of the videos can leave feedback and questions in which the author of the video can utilize to further the learning. Among the feedback of this specific video was a reoccurring question about handling lost or forgotten passwords for the asymmetric key.

As a quick refresher: When an asymmetric key is created you have the option to protect the key with a password. The password is required to open the key for decryption as well as modification of the key itself. Once the key is used to encrypt data its decryption is dependent upon that key.

We are all very familiar with login passwords. These are keyed in by end users to gain access to a system, network or database. Any network administrator would tell you that having to reset forgotten or lost passwords is a regular task. Therefore a natural question that arises when discussing key passwords is how to handle the occasional forgotten or lost password.

Unlike a login password, the key passwords are not typically keyed in by the end user. These passwords are passed to the decryption method programmatically. A developer may incorporate the key password in the user interface code and pass it to the database. The DBA may maintain a user defined function  or a key vault that returns the key password when called from a stored procedure that performs the decryption. The end user is not aware of the password requirement.

If the key password is lost or forgotten, it is a very dire situation. There are no reset functionality associated with the password for encryption keys. With the lost password so goes the data that the key secured. A question that may arise at this point might be: "If this unfortunate situation occurs can the data be decrypted without the key in which it was encrypted?" The intent of encryption is to protect data so that the possession and use of the key that encrypted it is required. If a keyless decryption attempt is successful, you may want to seek another method of encrypting your data.

No doubt, this is a scary prospect. For some the potential for this results in the rejection of utilizing encryption; but there are a couple of items to be aware of that does reduce or prevent this situation from occurring:

Database Master Key
Consider protecting your key with the Database Master Key (DMK) instead of a password. A database master key a key that is created at the database level and is used to protect various keys within the database. For more information about SQL Server Encryption Key Hierarchy, check out the following link: http://technet.microsoft.com/en-us/library/ms189586.aspx

Once a asymmetric key is created you can change the key protection from password to DMK by executing the ALTER command. Below is an example for altering an asymmetric key to use the DMK:

ALTER ASYMMETRIC KEY MyAsymmetricKey
    WITH PRIVATE KEY (
        DECRYPTION BY PASSWORD = [Current Password]
                     )

The inclusion of the "Decryption By Password" opens the asymmetric key with the current key password. By not including the "Encryption By..." option the key will be protected by the DMK.

Key Management
The "set it and forget it" approach to managing your keys increases the potential loss in the event that the key password is lost or forgotten. Rotating the keys through a key lifecycle, retiring keys and generating new ones, reduces this risk. SQL Server 2008 offers Extensible Key Management which provide the ability to utilize third-party tools that are specifically designed for key management tasks.

This specific blog entry focused on asymmetric keys; but the same considerations apply when using symmetric keys or certificates.