Take the 2-minute tour ×
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.

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  
Assuming you're using a route like this msdn talks about, you know the data is encrypted but SQL Server only sees it as varbinary(n). Am I missing something? –  billinkc Jan 15 at 19:32
1  
How do you even identify one such column manually? Just the fact that it contains varbinary data? –  Aaron Bertrand Jan 15 at 19:32
    
That's what I was afraid of. But with 20k columns to check, filtering the list down to varbinary data types will make it a bit easier. Honestly, I was hoping for something like an "IsEnc" flag or a DMV that keeps track of which columns the server has run encryption/decryption procedures on ... –  efesar Jan 15 at 19:48
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 at 19:57
    
No, SQL Server doesn't track whether some value has had some function run against it before it is written to a table, sorry. –  Aaron Bertrand Jan 15 at 20:03

2 Answers 2

up vote 4 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
    
This looks great. So far, just a quick selection using the KEY_NAME() function on an encrypted varbinary column did return a key name and conversely on a non-encrypted varbinary column it returned NULL. Also, as per Aaron's comment above, I tried the binary data type which turns out to be an invalid data type for the KEY_NAME() function. I'll test the full search feature and let you know my results soon. –  efesar Jan 16 at 16:46
    
Two minor issues. First, the KEY_NAME() column accepts a VARBINARY(8000) and bombs when it encounters a VARBINARY(MAX) - Bad Microsoft! I got around that by casting @Col in the SET SQL statement to VARCHAR(8000). Second, the SELECT/DELETE often won't work if you don't filter or order the SELECT because SQL statements are not guaranteed to be ordered and in my case this one wasn't (in fact, ID = 1 was ordered last in my test). Also to make it run faster, I used SELECT TOP 1 instead of DISTINCT (11 seconds vs 14 minutes). With these changes, I believe I could change my accepted answer! –  efesar Jan 16 at 17:38
1  
@efesar, Thnx for the feedback! Nice to here back if something worked or not! I forgot the where clause in the select statement in the loop. It just worked for me by accident since I was testing with 1 record. My bad. Regarding the varbinary(max), sys.Columns has a column called max_length, this is -1 for datatypes that are defined as MAX. I've edited the asnwer to reflect these changes. I added the distinct because you most likely have more than one record encrypted per key. –  Edward Dortland Jan 16 at 18:28
    
You're welcome. Also, a correction to my previous comment: VARBINARY(8000) not VARCHAR(8000). –  efesar Jan 16 at 22:45

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 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 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 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 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 at 20:05

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.