Viewing encrypted data using Trace
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;
GO
Create Database TestEncryption01
GO
USE TestEncryption01;
GO
--Create the seniorEmployees table
IF EXISTS (SELECT name from sys.tables where name='SeniorEmployees')
DROP TABLE dbo.SeniorEmployees
GO
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]
GO
ALTER TABLE dbo.SeniorEmployees ADD CONSTRAINT
PK_SeniorEmployees PRIMARY KEY CLUSTERED
(
ID
) 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:
go
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
GO
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]
GO
ALTER TABLE dbo.SeniorEmployees2 ADD CONSTRAINT
PK_SeniorEmployees2 PRIMARY KEY CLUSTERED
(
ID
) 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
