Get encrypted column name with their encryption key and certificate in sql server

Question

I am using SQL Server 2008/2012.In my database I have a table which has encrypted column. The column is encrypted by using following query -

Create the example table

CREATE TABLE [HR].[Employees](
      [EmployeeID]      [int]         NOT NULL,
      [EmployeeName]    [varchar](50) NULL,
      [SSN]             [varchar](20) NOT NULL,
      [EncryptedSSN]    [varbinary] (200) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(     [EmployeeID] ASC)
)
GO

Set up the Master Key

CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'T3stP@ssword'
GO

Create the Symmetric Key and CERTIFICATE

CREATE CERTIFICATE TestCert
   WITH SUBJECT = 'SSN Encryption';
GO

CREATE SYMMETRIC KEY HRKey
    WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE TestCert;
GO

Encrypt Data

OPEN SYMMETRIC KEY HRKey
   DECRYPTION BY CERTIFICATE TestCert;
Now we can update the EncryptedSSN column of our Employees table.

UPDATE [HR].[Employees]
   SET [EncryptedSSN] = EncryptByKey(Key_GUID('HRKey'), SSN);
GO

I am decrypting this data by using the query -

OPEN SYMMETRIC KEY HRKey
   DECRYPTION BY CERTIFICATE TestCert;
SELECT [SSN], 
    CONVERT(VARCHAR, DecryptByKey([EncryptedSSN])) 
    AS 'Decrypted SSN'
    FROM [HR].[Employees]
GO

Here we give key/ certificate value hardcoded.

I am getting the encrypted column name with their table by this query -

SELECT stab.name Table_Name, sc.name Column_Name FROM sys.columns sc
        INNER JOIN sys.types st ON sc.system_type_id=st.system_type_id
        INNER JOIN sys.tables stab ON stab.object_id=sc.object_id
        WHERE st.name='varbinary'
        AND stab.is_ms_shipped=0

And the key and certificate list by this query -

SELECT name, key_length, algorithm_desc, create_date, modify_date
FROM sys.symmetric_keys;
SELECT name, subject, start_date, expiry_date 
FROM sys.certificates

Now I want to get which key/certificate belongs to which encrypted column SO I can apply decryption on that column without giving hardcoded value. Please help me....

Answer 1

After search and try I found the solution that is -

SELECT DISTINCT key_name(encryptedcol) FROM encryptedTable;

This query gives result the encrypted key which is belong to that column.

Answer 2

I am using SQL Server 2016 .

Below here is the query to get all required Encrypted columns with key.

SELECT t.name AS TableName
    ,c.name AS ColumnName
    ,c.max_length
    ,k.name AS KeyName
    ,c.encryption_type_desc
    ,c.encryption_algorithm_name
FROM sys.columns c
INNER JOIN sys.column_encryption_keys k ON c.column_encryption_key_id = k.column_encryption_key_id
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE encryption_type IS NOT NULL    

Answer 3

You can't, at least using T-SQL built-in system functions and views.

Also, a column's type can be varbinary and the data there is not necessary to be encrypted.

If you want to build dynamic T-SQL statements and to avoid certificates and keys hard-coding, then you can create a mapping table. For example, something like this:

TableName
ColumnName
Certificate
EncryptionKey
IsSymmetric

It is not the perfect work-around (yes,you need to maintained the data), but if new columns are not encrypted constantly it may do the job.