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):

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