Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data?

I need to nullify the data in all encrypted columns in a development server (according to our business rules). I know most of the columns because we use them regularly, but I want to be thorough and also I want to be able to prove that I've found them all.

I've searched the web, looked in INFORMATION_SCHEMA and checked the DMVs I thought would be useful and also sys.columns and sys.objects -- but so far no luck.

share|improve this question
1  
We are lazy here and have computed columns that use autodecryptbyvoodoo. If you something similar, then you could look at column definitions for that – billinkc Jan 15 '14 at 19:57
up vote 7 down vote accepted

Assuming you are talking about data that is encrypted with SQL Server keys, there is way to find these columns.

The Key_name() Function will return the name of the key used for the encryption for that particular value and will return NULL if there isn't anything encrypted with a "known" key (3rd party, or simple not encrypted).

With that knowlegde we can test every column to see if it contains at least one row which has a varbinary value that returns a key name

functionality of key_name()

--create a test database
CREATE DATABASE [Test_ENCR]
GO

--change context
USE [Test_ENCR]
GO


--because it's possible to encrypt different rows with different keys I'll create 2 keys for this demo
-- Create a symmetric key
CREATE SYMMETRIC KEY symmetricKey1
   WITH ALGORITHM = AES_128
   ENCRYPTION BY PASSWORD = 'password01!';
GO

-- Create a second key
CREATE SYMMETRIC KEY symmetricKey2 
   WITH ALGORITHM = AES_128
   ENCRYPTION BY PASSWORD = 'password02!';
GO



--create a table that will have a column holding:
--1: encrypted row with key1
--2: encrypted row with key2
--3: a non encrypted just varbinary value

CREATE TABLE encryptedTable
(ID int IDENTITY PRIMARY KEY,
EncryptedCol varbinary(256) NOT NULL);
GO


-- open key1
OPEN SYMMETRIC KEY symmetricKey1 
    DECRYPTION BY PASSWORD = 'password01!';
GO

-- open key2
OPEN SYMMETRIC KEY symmetricKey2 
    DECRYPTION BY PASSWORD = 'password02!';
GO

--insert encrypted data with key1
INSERT INTO encryptedTable(encryptedCol)
VALUES ( ENCRYPTBYKEY (Key_GUID('symmetricKey1'), 'EncryptedText1'));
GO

--insert encrypted data with key2
INSERT INTO encryptedTable(encryptedCol)
VALUES ( ENCRYPTBYKEY (Key_GUID('symmetricKey2'), 'EncryptedText2'));
GO


--insert just varbinary data
INSERT INTO encryptedTable(encryptedCol)
VALUES (CONVERT(varbinary(256),'NotEncryptedTextJustVarBinary'))



--have a look, without the key, all varbinary for you.
SELECT * FROM encryptedTable
GO

results:

enter image description here

--Return all key_names
SELECT DISTINCT     key_name(encryptedcol), 
                    EncryptedCol 
FROM encryptedTable;

results:

enter image description here

How to implement it to find encrypted columns

--How do we dynamically find all the columns that have at least one row with a encrypted value?

-- first we will find all tables and column with a varbinary datatype
-- then we will test all those columns with a simple select
-- If the key_name() function returns a value, the column and table name are stored together with the keyname

--create a table to hold all varbinary columns and tables
CREATE TABLE #TablesWithVarbinCols (    ID int IDENTITY,
                                TableName nvarchar(128),
                                ColumnName nvarchar(128)
                                );

--create a table to hold the end result
CREATE TABLE #TablesWithEncryption (
                                TableName nvarchar(128),
                                ColumnName nvarchar(128),
                                KeyName varchar(128)
                                );


--find and store all table and column names of user tables containing a varbinary column
INSERT INTO #TablesWithVarbinCols (TableName,ColumnName)
SELECT      o.[name] as TableName,
            c.[name] as ColumnName
FROM        sys.objects o
INNER JOIN  sys.columns c
ON          o.[object_id]=c.[object_id] 
INNER JOIN  sys.types t
ON          c.system_type_id=t.system_type_id
WHERE       o.[type]='U'
AND         t.name=N'varbinary'
AND         c.max_length > -1;


DECLARE @col nvarchar(256)
DECLARE @tab nvarchar(256)
DECLARE @c int = 1
DECLARE @MaxC int
DECLARE @SQL varchar(max)

SELECT @MaxC=MAX(ID)
FROM #TablesWithVarbinCols

--loop the previous result and create a simple select statement with a key_name() is not null where clause. 
--If you have a result, store the details
WHILE @c <= @MaxC
BEGIN
    SELECT  @Tab=TableName,
         @col=ColumnName
    FROM    #TablesWithVarbinCols
    WHERE   ID=@c

    SET @SQL='  INSERT INTO #TablesWithEncryption (TableName, ColumnName, KeyName)
                SELECT DISTINCT '''+@Tab +''',''' +@col +''', key_name('+@Col +') from '+ @tab +' 
                WHERE key_name('+@Col +') is not null;'
    exec (@SQL)

    DELETE
    FROM #TablesWithVarbinCols
    WHERE id=@c;
    SET @c=@c+1
END

--select the result
SELECT * FROM #TablesWithEncryption;

results:

enter image description here

--cleanup
DROP TABLE #TablesWithVarbinCols;
DROP TABLE #TablesWithEncryption;
share|improve this answer

The problem with cell level encryption is that the column itself isn't really encrypted, it's the data contained in that column. The columns themselves are just varbinary columns (because that's what's required) and could contain completely legible data. It's the use of the ENCRYPTBY* and DECRYPTBY* functions that truly make the data encrypted.

You can start by simply querying the sys.columns view for all columns that are varbinary:

select
  object_name(a.object_id) [objectname]
  ,a.name [columnname]
  ,a.column_id
from
   sys.columns a
   join sys.types b on (a.system_type_id = b.system_type_id)
where
   b.name = N'varbinary';

Otherwise, you'll need to review your code to identify where the encryption/decryption functions are being used:

select
   object_name(object_id) [objectname]
   ,definition
from
   sys.sql_modules
where
   definition like N'%ENCRYPT%' 
   OR definition like N'%DECRYPT%';
share|improve this answer
2  
You may want binary too. I'd just hard-code the system_type_id values rather than join to sys.types; those aren't going to change. Also, just to be complete, the T-SQL ENCRYPT() functions might be called from outside the database in the application code, or they may not be used at all (e.g. the app may encrypt the data before any SQL is called). – Aaron Bertrand Jan 15 '14 at 19:55
    
That's very helpful. Searching the modules table is something I hadn't considered. Unfortunately, most of the code is written in Lync & Entity Framework. I suppose I could search the procedure cache the same way (sys.dm_exec_sql_text)? What do you think? – efesar Jan 15 '14 at 19:56
    
@efesar search the code, not the procedure cache. Not all of your plans will be in the cache, but all your code will always be there. There must be some way you can identify when Entity Framework will or will not decide to encrypt some piece of data. – Aaron Bertrand Jan 15 '14 at 20:01
    
That's a good suggestion but I'm not the developer and I don't have access to that codebase. Another option would be to run traces to catch as many of the encryption/decryption statements as possible. Thanks all. I think this answer has enough information in it to be the accepted answer. – efesar Jan 15 '14 at 20:05
2  
Yeah, it's just as easy to use the system ids. I joined it out to make the query more human readable. – Mike Fal Jan 15 '14 at 20:05

The code below will find all varbinary columns in a database that have been encrypted using a symmetric key present in SQL and is a little more efficient than some of the other solutions.

CREATE TABLE #EncryptedColumns (
        schema_id INT NOT NULL
        , object_id INT NOT NULL
        , column_id INT NOT NULL
        , schemaname SYSNAME NOT NULL
        , tablename SYSNAME NOT NULL
        , columnname SYSNAME NOT NULL
        , keyname SYSNAME NULL
        )

INSERT INTO #EncryptedColumns (
        schema_id
        , object_id
        , column_id
        , schemaname
        , tablename
        , columnname
        )
SELECT sys.tables.schema_id
        , sys.tables.object_id
        , sys.columns.column_id
        , SCHEMA_NAME(sys.tables.schema_id) AS schemaname
        , sys.tables.NAME AS tablename
        , sys.columns.NAME AS columnname
FROM sys.columns
INNER JOIN sys.tables
        ON sys.columns.object_id = sys.tables.object_id
WHERE (sys.columns.system_type_id = TYPE_ID('varbinary'))
        AND (sys.tables.is_ms_shipped = 0)

DECLARE potentiallyEncrypted CURSOR LOCAL FAST_FORWARD
FOR
SELECT schema_id
        , object_id
        , column_id
        , schemaname
        , tablename
        , columnname
FROM #EncryptedColumns

DECLARE @schema_id INT
DECLARE @object_id INT
DECLARE @column_id INT
DECLARE @schemaname SYSNAME
DECLARE @tablename SYSNAME
DECLARE @columnname SYSNAME

DECLARE @keyCheckStatement NVARCHAR(MAX)
DECLARE @RC INT

OPEN potentiallyEncrypted

FETCH NEXT
FROM potentiallyEncrypted
INTO @schema_id
        , @object_id
        , @column_id
        , @schemaname
        , @tablename
        , @columnname

WHILE (@@FETCH_STATUS = 0)
BEGIN
        SET @keyCheckStatement = N'
UPDATE #EncryptedColumns
SET keyname = (
                SELECT TOP 1 KEY_NAME(CONVERT(VARBINARY(8000), ' + QUOTENAME(@columnname) + N')) AS keyname
                FROM ' + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N'
                WHERE NOT ' + QUOTENAME(@columnname) + N' IS NULL
              )
WHERE #EncryptedColumns.object_id = @object_id
        AND #EncryptedColumns.column_id = @column_id'

        EXEC @RC = sp_executesql @keyCheckStatement
                , N'@object_id int, @column_id int'
                , @object_id = @object_id
                , @column_id = @column_id

        FETCH NEXT
        FROM potentiallyEncrypted
        INTO @schema_id
                , @object_id
                , @column_id
                , @schemaname
                , @tablename
                , @columnname
END

CLOSE potentiallyEncrypted
DEALLOCATE potentiallyEncrypted

DELETE
FROM #EncryptedColumns
WHERE keyname IS NULL

SELECT schema_id
        , object_id
        , column_id
        , schemaname
        , tablename
        , columnname
        , keyname
FROM #EncryptedColumns

DROP TABLE #EncryptedColumns
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.