Viewing Keys

The SQL Server provides several SQL statements to list existing cryptographic keys. The first set of statements is used to show the SQL Server keys, both keys stored internally and keys with references to EKM providers. This key listing is separated into symmetric and asymmetric listings.

Use the next SQL statement to show current symmetric keys:

SQL Statement

SELECT * from master.sys.symmetric_keys

This will show a listing of current asymmetric keys:

SQL Statement

SELECT * from master.sys.asymmetric_keys

Remember that only these keys can be used in SQL statements.

Additionally, all keys stored in the CryptoServer SQLEKM provider can be listed with an extra SQL statement. These keys can be used in a CREATE … KEY statement as PROVIDER_KEY_NAME to create an SQL Server key binding.

SQL Statement

SELECT * FROM sys.dm_cryptographic_provider_keys(65536)

Note that the number of shown keys can differ between previous mentioned SQL statements since not all keys stored in CryptoServer SQLEKM provider necessarily have an equivalent key in the SQL Server space and vice versa.