Filtering (obfuscating) Sensitive Text in SQL Server

  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