First published on MSDN on Jun 11, 2009
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
Updated Mar 23, 2019
Version 2.0raulgarciamsft
Microsoft
Joined February 08, 2019
SQL Server Blog
Follow this blog board to get notified when there's new activity