Compartilhar via

Filtering (obfuscating) Sensitive Text in SQL Server

  • Artigo

  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

Comments

  • Anonymous
    April 04, 2011
    The comment has been removed
  • Anonymous
    April 05, 2011
     It is obfuscation of potentially sensitive data from traces. As you mentioned, this behavior may be used to try to avoid tracing, but as shown on the last example, it cannot obfuscate auditing events. In the sample I included you can clearly see the “CREATE SYMMETRIC KEY” & "OPEN SYMMETRIC KEY" statements (with obfuscated password)even though the batch-starting & batch-completed events are obfuscated. -Raul Garcia