Viewing Tables and Checking Keys

Viewing tables

Using SQL Server Management Studio

To check that data in a table was either encrypted or decrypted successfully, complete the following steps with SQL Server Management Studio:

1. Go to Databases > TestDatabase > Tables.

2. Right-click the table name and select Select Top 1000 Rows to view the encrypted or decrypted data.

Using SQL Query

To check that data in a table was either encrypted or decrypted successfully, execute the following SQL query:

Use TestDatabase
SELECT * FROM <table_name>

Checking keys

The following queries show how you can check the attributes of keys in your database and the KeyControl SQLEKM provider.

• To view the symmetric keys in a database:

  Copy

  Use TestDatabase
  SELECT * FROM sys.symmetric_keys

• To view the asymmetric keys in a database:

  Copy

  Use TestDatabase
  SELECT * FROM sys.asymmetric_keys

• To list all the KeyControl SQLEKM provider keys that are associated with the current credential:

  Copy

  DECLARE @ProviderId int;
  SET @ProviderId = (SELECT TOP(1) provider_id
  FROM sys.dm_cryptographic_provider_properties
  WHERE friendly_name LIKE '<provider friendly_name>');
  SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId);
  GO

Where <provider friendly_name> can be found as shown in Viewing Tables and Checking Keys .

• To correlate symmetric keys between the database and the KeyControl SQLEKM provider:

  Copy

  DECLARE @ProviderId int; 
  SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties WHERE friendly_name LIKE '<provider friendly_name>');
  SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId) FULL OUTER JOIN sys.symmetric_keys
  ON sys.symmetric_keys.key_thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint WHERE sys.dm_cryptographic_provider_keys.key_type = 'SYMMETRIC KEY'
  GO

  where <provider friendly_name> can be found as shown in Viewing Tables and Checking Keys .

• To correlate asymmetric keys between the database and the KeyControl SQLEKM provider:

  Copy

  DECLARE @ProviderId int;
  SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties
  WHERE friendly_name LIKE '<provider friendly_name>');
  SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId)
  FULL OUTER JOIN sys.asymmetric_keys
  ON sys.asymmetric_keys.thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
  WHERE sys.dm_cryptographic_provider_keys.key_type = 'ASYMMETRIC KEY'
  GO

  where <provider friendly_name> can be found as shown in Viewing Tables and Checking Keys .

• To correlate all keys (symmetric and asymmetric) between the database and cryptographic provider:

  Copy

  DECLARE @ProviderId int;
  SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties
  WHERE friendly_name LIKE '<provider friendly_name>');
  SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId)
  FULL OUTER JOIN sys.symmetric_keys
  ON sys.symmetric_keys.key_thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
  FULL OUTER JOIN sys.asymmetric_keys
  ON sys.asymmetric_keys.thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
  GO

  where <provider friendly_name> can be found as shown in Viewing Tables and Checking Keys .

  The `key_thumbprint` returned by the T-SQL queries, maps to the `key ID` when listing keys with the REST API.