Viewing encrypted data using Trace

Article
12/18/2013

Today morning I got an email from a customer to review some design recommendation. In the recommendation, another DBA claimed that encrypting the data by using EncryptByKey or EncryptByPassPhrase is not secured because we can trace the data and read it as plain text.

This is not correct and if you have the patience to go through the rest of the post you will get the proof. That goes for both profiler trace and extended events as well. However network sniffing is possible which can be prevented by SSL encryption to the data in the wire but that's outside the scope of this post.

USE master;

Create Database TestEncryption01

USE TestEncryption01;

--Create the seniorEmployees table

IF EXISTS (SELECT name from sys.tables where name='SeniorEmployees')

DROP TABLE dbo.SeniorEmployees

CREATE TABLE dbo.SeniorEmployees

(

ID int NOT NULL,

EmployeeID varbinary(MAX) NOT NULL,

EmployeeSalary decimal(8, 2) NOT NULL,

EmployeePosition varbinary(MAX) NOT NULL

) ON [PRIMARY]

TEXTIMAGE_ON [PRIMARY]

ALTER TABLE dbo.SeniorEmployees ADD CONSTRAINT

PK_SeniorEmployees PRIMARY KEY CLUSTERED

(

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

--CREATE database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Str0ngPassw0rd!'

--Create certificate first

CREATE CERTIFICATE EmployeesCert WITH SUBJECT='Certificate for encrypting symmetric keys';

--check the certificate

SELECT * FROM sys.certificates

--now create the symmetric key

CREATE SYMMETRIC KEY EmployeesKey WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE EmployeesCert;

--Key meta data

SELECT * FROM sys.symmetric_keys WHERE name='EmployeesKey'

Now let's stop here a moment to create a quick trace. I used a small trace definition to track the SQL:batchCompleted event, below is the output for the definition, the trace filters by the database name that we created above.

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

exec sp_trace_setevent @TraceID, 12, 16, @on

exec sp_trace_setevent @TraceID, 12, 35, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'TestEncryption01'

-- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

finish:

Now after setup the trace and running it, let's go back to management studio to try inserting and selecting from the encrypted column using the keys

--now use the key to insert some records

OPEN SYMMETRIC KEY EmployeesKey DECRYPTION BY CERTIFICATE EmployeesCert;

DECLARE @X INT=1

WHILE @X<=1000

BEGIN

INSERT INTO SeniorEmployees(ID,EmployeeID,EmployeeSalary,EmployeePosition)

VALUES(@X,ENCRYPTBYKEY(KEY_GUID('EmployeesKey'),CONVERT(NVARCHAR(20),CEILING(RAND()*999999)),1,CONVERT(varbinary,@X)),

CEILING(RAND()*999999),

ENCRYPTBYKEY(KEY_GUID('EmployeesKey'),N'BOSS#' + CONVERT(NCHAR(4),@X),1,CONVERT(varbinary,@X)))

SET @X=@X+1;

END

CLOSE SYMMETRIC KEY EmployeesKey;

--Selecting WITH THE KEYS

OPEN SYMMETRIC KEY EmployeesKey DECRYPTION BY CERTIFICATE EmployeesCert;

SELECT ID,CONVERT(NVARCHAR(20),DECRYPTBYKEY(EmployeeID,1,CONVERT(VARBINARY,ID))) as 'EmployeeID',EmployeeSalary,

CONVERT(NVARCHAR(20),DECRYPTBYKEY(EmployeePosition,1,CONVERT(VARBINARY,ID))) as 'EmployeePosition'

FROM SeniorEmployees

CLOSE SYMMETRIC KEY EmployeesKey;

In the trace when we open the file in the profiler here's what we will get. The statement removed for both the insert and select statement and replaced by comments for protecting the encrypted data.

Same goes for the EncryptByPassPhrase function as well, continue with the remaining sample while running the trace

Encrypt by pass phrase.

--Create a new table

IF EXISTS (SELECT name from sys.tables where name='SeniorEmployees2')

DROP TABLE dbo.SeniorEmployees2

CREATE TABLE dbo.SeniorEmployees2

(

ID int NOT NULL,

EmployeeID varbinary(MAX) NOT NULL,

EmployeeSalary decimal(8, 2) NOT NULL,

EmployeePosition varbinary(MAX) NOT NULL

) ON [PRIMARY]

TEXTIMAGE_ON [PRIMARY]

ALTER TABLE dbo.SeniorEmployees2 ADD CONSTRAINT

PK_SeniorEmployees2 PRIMARY KEY CLUSTERED

(

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

/*Insert by encryptByPassPhrase*/

DECLARE @X INT=1

WHILE @X<=1000

BEGIN

INSERT INTO SeniorEmployees2(ID,EmployeeID,EmployeeSalary,EmployeePosition)

VALUES(@X,EncryptByPassPhrase('This is My Pass Phrase',CONVERT(NVARCHAR(20),CEILING(RAND()*999999)),1,CONVERT(varbinary,@X)),

CEILING(RAND()*999999),

EncryptByPassPhrase('This is My Pass Phrase',N'BOSS#' + CONVERT(NCHAR(4),@X),1,CONVERT(varbinary,@X)))

SET @X=@X+1;

END

/*Select by DecryptByPassPhrase */

SELECT ID,CONVERT(NVARCHAR(20),DecryptByPassPhrase('This is My Pass Phrase',EmployeeID,1,CONVERT(VARBINARY,ID))) as 'EmployeeID',EmployeeSalary,

CONVERT(NVARCHAR(20),DecryptByPassPhrase('This is My Pass Phrase',EmployeePosition,1,CONVERT(VARBINARY,ID))) as 'EmployeePosition'

FROM SeniorEmployees2

And here's the view from the trace.

And for Extended Events as well, here's a quick screenshot of the XE outoupt

Viewing encrypted data using Trace

Additional resources