Blog Post

SQL Server Blog
1 MIN READ

Filtering (obfuscating) Sensitive Text in SQL Server

raulgarciamsft's avatar
Mar 23, 2019
First published on MSDN on Jun 11, 2009

A very common concern when dealing with sensitive data such as passwords is how to make sure that such data is not exposed through traces. SQL Server can detect and filter the SQL statements in traces that include the usage of DDL and built-ins (such as OPEN SYMMETRIC KEY, and EncryptByKey) that are known to include potentially sensitive data. For example:

-- T-SQL Script:

-- Create an open a symmetric key with password

--

CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = 'D3m0 p4SSw0Rd&'

go

OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = 'D3m0 p4SSw0Rd&'

go

The resulting traces should look similar to the following (including all batch starting/complete and audit events, but for simplicity including only some of the relevant columns):

Event class

TextData

Event subclass

Object name

Object Type

SQL: Batch Starting

--*CREATE SYMMETRIC KEY---------------…

Audit: DB Object Mgr

CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = '******'

1 - Create

key00

19283 - SK

SQL: Batch Completed

--*CREATE SYMMETRIC KEY------------------

SQL: Batch Starting

--*OPEN SYMMETRIC KEY-------------------

Audit: DB Object Mgr

OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = '******'

10 - Open

key00

19283 - SK

SQL: Batch Completed

--*OPEN SYMMETRIC KEY----------------…

Unfortunately when calling such statements via dynamic SQL or when using user defined st

Updated Mar 23, 2019
Version 2.0
No CommentsBe the first to comment