The sys.symmetric_keys Catalog View

2014-06-16 - Cryptography, DMVs & CVs, General, Security, Security

Introduction

Similar to the sys.asymmetric_keys catalog view that allows us to inspect the properties of existing asymmetric keys, the sys.symmetric_keys catalog view can be used to inquire about existing symmetric keys.

A sys.symmetric_keys Example

The sys.symmetric_keys catalog view returns a single row for each symmetric key in the current database. The following statement shows the columns available:

SELECT SK.name,
       SK.principal_id,
       SK.symmetric_key_id,
       SK.key_length,
       SK.key_algorithm,
       SK.algorithm_desc,
       SK.create_date,
       SK.modify_date,
       SK.key_guid,
       SK.key_thumbprint,
       SK.provider_type,
       SK.cryptographic_provider_guid,
       SK.cryptographic_provider_algid
  FROM sys.symmetric_keys AS SK;

The name column returns the name of the key. The principal_id specifies the database principal that owns the key. The symmetric_key_id is an integer identifier for the key. It is unique within a single database.

key_length, key_algorithm and algorithm_desc are the cryptographic properties of the key. They describe the algorithm that the key can be used with, as well as the number of bits that make up the key.

The create_date column lets us know when the key was created in the current database. The modify_date column on the other hand informs us when the ALTER SYMMETRIC KEY statement was last used on the key.

The key_guid column is a globally unique identifier for the key. This guid is part of the encrypted value and is used by the DECRYPTBYKEY function to identify the correct key to use.

The remaining values, key_thumbprint, provider_type, cryptographic_provider_guid and cryptographic_provider_algid are used for keys provided by an EKM module. For SQL Server generated keys, all four columns return NULL.

The output of the above query is shown in the image below.

sys.symmetric_keys in Action.

Summary

The sys.symmetric_keys catalog view returns the properties of all symmetric keys in the current database.

Categories: Cryptography, DMVs & CVs, General, Security, Security
Tags: , , , ,

Trackbacks

  1. […] key_id column contains the id of the symmetric key and can be used to join to the sys.symmetric_keys catalog […]

  2. […] the following script creates two similar keys, one temporary and one non-temporary. It then queries the sys.symmetric_keys catalog view in both the current database and tempdb for all keys that contain the four letters "temp" in their […]