Filtering (obfuscating) Sensitive Text in SQL Server

Filtering (obfuscating) Sensitive Text in SQL Server

Rate This
  • Comments 2

  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 stored procedures (following the best practices in order to minimize SQL injection potential), SQL Server engine has no way to identify that the underlying statements will involve any of the DDL or built-ins marked to be filtered. For example:

EXEC( 'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256   ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''')

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

 

Event class

TextData

Event subclass

Object name

Object Type

SQL: Batch Starting

EXEC( 'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256   ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''')

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

 

 

 

Audit: DB Object Mgr

 CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256

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

1 - Create

key00

19283 - SK

Audit: DB Object Mgr

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

10 - Open

key00

19283 - SK

SQL: Batch Completed

EXEC( 'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256   ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''')

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

 

 

 

 

  In such cases, the audit events based on the DDL execution will be correctly filtered, but the SQL events (batch starting/completed) cannot be filtered as the system has no information specific to the string containing the T-SQL script to be executed.

  While there is no mechanism available to register user defined for filtering, there is a potential workaround that may help. The idea is to force the filtering mechanism by having a builtin that we know will be filtered, but avoid executing it if possible. For example:

 -- T-SQL Script:

-- Create an open a symmetric key with password

--

DECLARE @Secret nvarchar(max)

-- The conditional expression will always be true,

-- therefore it will always select the statement

-- but the inclusion of EncryptByPassphrase will

-- enable the trace filtering mechanism

--

SELECT @Secret = CASE WHEN 1=1 THEN

   'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&'''

   ELSE EncryptByPassphrase('','') END

EXEC(@Secret)

SELECT @Secret = CASE WHEN 1=1 THEN

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

   ELSE EncryptByPassphrase('','') END

EXEC(@Secret)

go

 

As you can see in the following traces, the assignment sections of the batch will be replaced, filtering the sensitive data, but the auditing information is still available.

Event class

TextData

Event subclass

Object name

Object Type

SQL: Batch Starting

DECLARE @Secret nvarchar(max)

--*ASSIGN---------------------------

EXEC(@Secret)

--*ASSIGN---------------------------

EXEC(@Secret)

 

 

 

Audit: DB Object Mgr

 CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256

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

1 - Create

key00

19283 - SK

Audit: DB Object Mgr

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

10 - Open

key00

19283 - SK

SQL: Batch Completed

DECLARE @Secret nvarchar(max)

--*ASSIGN---------------------------

EXEC(@Secret)

--*ASSIGN---------------------------

EXEC(@Secret)

 

 

 

 

It is important to remark that audit events (as well as audit events based on SQL Server 2008 architecture) are working the same way.

 -Raul Garcia
  SDE/T
  SQL Server Engine

Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post
  • Hi,

    Is this really filtering or obfuscation? can this behavior be disabled?

    I worry it could be exploited for trace avoidance.

  •  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

Page 1 of 1 (2 items)