The sys.key_encryptions Catalog View

Introduction

All encryption keys and certificates in SQL Server are encrypted by another key or a password. Which mechanism is used, you specify when creating the key using the ENCRYPTION BY clause. Symmetric keys however are a little different. They can be altered after creation to be encrypted by more than one key or password at the same time. For that reason, there is a special catalog view that returns all encryptions for the symmetric keys in the current database.

A sys.key_encryptions Example

The sys.key_encryptions catalog view has the following columns:

The key_id column contains the id of the symmetric key and can be used to join to the sys.symmetric_keys catalog view.

The two columns crypt_type and crypt_type_desc let us know if the encryption is based on a password, an asymmetric key or any of the other encryption options. In the later column you will accordingly find values like ENCRYPTION BY PASSWORD or ENCRYPTION BY ASYMMETRIC KEY.

For an encryption that is based on another key in the database, the thumbprint can be used to identify that key. If you for example look at the sys.asymmetric_keys catalog view you will find that same thumbprint value there too.

The crypt_property column finally contains the actual encrypted key. If the key is encrypted with another symmetric or asymmetric key, you can actually use the SQL Server decryption functions to get the unencrypted key from this value.

Below is the output of above query on my system:

There are three different keys shown in the output with a total of six key encryptions.

Summary

Symmetric keys in SQL Server can be encrypted by more than one key or password at the same time. The sys.key_encryptions catalog view can be used to identify the different encryptions for all symmetric keys in the current database.