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&'
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
ENCRYPTION BY PASSWORD = '******'
1 - Create
key00
19283 - SK
SQL: Batch Completed
--*CREATE SYMMETRIC KEY------------------
--*OPEN SYMMETRIC KEY-------------------
OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = '******'
10 - Open
--*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&''')
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:
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)
'OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&'''
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.
--*ASSIGN---------------------------
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
My teammate Lyudmila is maintaining her own TechNet blog where she writes articles related to SQL Server security. You can access her blog at http://blogs.technet.com/lyudmila_fokina. Her blog is written in Russian, but the samples she includes should be easy to follow, and you can use an online translation tool for the rest of the text.
Her latest article is a very interesting suggestion on how to use SQL Server Agent jobs to provide a scheduled temporary elevation of privileges. You can find the whole article at http://blogs.technet.com/lyudmila_fokina/archive/2009/06/11/sql-server-agent-jobs.aspx.