The sys.asymmetric_keys Catalog View

Introduction

As with SQL Server certificates, occasionally we need to find out more about the asymmetric keys that exist in a database. In such a case we can use a catalog view that was provided just for this purpose: sys.asymmetric_keys.

Using the sys.asymmetric_keys Catalog View

The sys.asymmetric_keys catalog view returns a single row for every asymmetric key that exists in the current database. It has the following columns:

The name column returns the name provided when the asymmetric key was created. The asymmetric_key_id is the SQL Server identifier for the key. Both values are unique within each database.

The principal_id references the database principal that owns the key. In most cases that is the user that created the key.

The two columns pvt_key_encryption_type and pvt_key_encryption_type_desc return information about the way in which the private key of this asymmetric key pair is encrypted. The most common values for pvt_key_encryption_type_desc are NO_PRIVATE_KEY, ENCRYPTED_BY_MASTER_KEY and ENCRYPTED_BY_PASSWORD. The pvt_key_encryption_type column returns the same information in a two-letter encoded form.

thumbprint and public_key return internal representations of the key in binary form. The thumbprint is a hash value that allows you to compare two keys. You can consider it a globally unique identifier for the asymmetric key. The public_key column returns the binary representation of just the public key.

The three columns algorithm, algorithm_desc and key_length return all the same information. As all algorithms available are from the RSA family, all three columns just provide us information about which of the three possible key lengths the private key uses.

The sid column is used when a database principal is created from this key. The string-sid contains the same value, in a string-encoded format.

attested_by contains a value that is not documented and provider_type, cryptographic_provide_guid and cryptographic_provider_algid are only used in the context of an Extensible Key Management module.

The image below shows the sys.asymmetric_keys catalog view in action:

Other than for certificates for which SQL Server provide several additional functions, the sys.asymmetric_keys catalog view is the only way to get information about the asymmetric keys in the current database.

Summary

The sys.asymmetric_keys catalog view allows us to probe into asymmetric keys that exist in the current database. Values returned include the name, the internal id, the owner's principal id, the key length and the type of protection used for the private key.