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