T-SQL Cryptographic Patterns – part 7: who was that masked man?

SQL Server 2012 gives the DBA a shove toward Extended Events – and away from SQL Trace – with regard to monitoring. The trace catalog views are marked for deprecation with advice to use Extended Events. Extended Events do a better job of decoupling the application workload and the monitoring workload. Extended Events provide a more complete one-stop event-ing experience over SQL Trace resulting in a somewhat more flexible and somewhat deeper insight into performance and security events. 

An SSMS Extended Events properties dialog + wizard are added in SQL Server 2012 that is similar to Profiler. Creating a session can be as easy as selecting a template and taking the defaults. Of course, T-SQL can also be used to admin event sessions or to query event targets or to query the extended events catalog views and DMVs.

Extended Events Sessions are better able to limit performance impact than trace.

  • For starters extended events takes the insert into the collection target – be that a ring buffer, table or file – out of the monitored user process.
  • Then, while creating the session, the EVENT_RETENTION_MODE setting resists the = NO_EVENT_LOSS value by throwing up an “are you sure” pop-up when selected that warns of potential performance issues – not unlike SQL Trace. The other two possible EVENT_RETENTION_MODEs indicate memory reclamation strategies that determine the data chunk size to be thrown on the floor when event output cannot keep up: ALLOW_SINGLE_EVENT_LOSS discards an event at a time and ALLOW_MULTIPLE_EVENT_LOSS discards a buffer of events at a time.
  • And, as shown in the UI screen shot, a filter operator on the session_id provides yet another way to throw the monitor’s collected data on the floor. The divides_by_unit64 operator means that events where the Field column value can be divided by Value column value evenly will be collected. A Value of 2 would cause the session to attempt to collect every other qualifying event. A value of 5 as shown below – the defaults from the Query Wait Statistics template – indicates that every 5th qualifying event will be collected into the session target.
Extended Event filter operators in SSMS 2012

Of course T-SQL’s CREATE/ALTER/DROP EVENT SESSION DDL will do everything the GUI will do.

SQL Server Event Output Obfuscation

SQL Server’s event-ing subsystems attempt to obfuscate sensitive information captured in the event output. SQL Trace, Alerts, Event Notifications, DDL Triggers, Extended Events and SQL Audit for example will all mask DDL passwords/phrases and statements involving a cryptographic method. However debuggers, OS/network trace tools and many other process monitors are not necessarily subject to this SQL Server event obfuscation.

It is tempting to want to exploit event output obfuscation in the effort to increase protection of sensitive data. Unfortunately SQL Server’s event output obfuscation provides no protection for text values passed to SQL Server or between sql_modules. This is a fundamental limitation for the pragmatic application of SQL Server cryptography: most data simply does not originate at the database. The best place and time to encrypt sensitive data is where the data is originated as it is originated. Waiting until data reaches the database to scramble the bits increases the window of opportunity for a leak or compromise – precisely the type of leak that SQL Server event obfuscation pretends to plug. When cipher text is the only data passed through the database connection, there is far less chance that the clear text might be unknowingly revealed to any snoops on the ride to database or to any snoops watching a SQL Server event output stream.

Similar obfuscation leaks can occur after the data reaches SQL Server. The more difficult offenders to identify are likely to be passed parameters and/or dynamic SQL. Assignment operations are also potentially problematic. Building a string using T-SQL assignment expressions that will subsequently be dynamically executed can leak while building the string and again when the dynamic statement is submitted for execution.

SQL Server originated events pull the clear text of any alpha-numeric value passed to a sql_module as clear text. AS a result, intercepting input using Profiler is simplistic given the necessary permission (ALTER TRACE). Proactive monitoring that can trigger intelligent remedies not only when intrusion is detected but any time that know-to-leak events are raised – is necessary to prevent obfuscation leaks: a monitor of system monitoring events…

In the balance, SQL Server event output obfuscation is not all that usable or even useful in its current state. Mostly, it’s just there. Not much the DBA can do with it or to it. In order to protect the event output it is necessary to combine the obfuscation that is in place with assignment operations for any sensitive values to successfully obfuscate the output. This is burdensome because the only way to know you have something that works is to collect events and study the output. In the context of protecting sensitive information such coding cost and complexity is unacceptable.

So, how could it be better?

Maybe if there were declarative guarantees of obfuscation for user designated columns and variables – in all circumstance and for all event subsystems. Sorta like a lock hint or an index – then event obfuscation could become a tool. But this is certainly not something Microsoft – or most any software vendor – would do without a loud clamor from customers. Since most applications store sensitive data as clear text in the database and/or sparingly use encryption as required for compliance to a minimal threshold, I see no reason to anticipate an uproar. No big deal I reckon. SQL Server was already a second rate place to implement most cryptography because it doesn’t originate much data.

Declarative control of obfuscation could help prevent called modules and dynamic SQL Server leaks in process, but has no effect on data passed in to SQL Server. If sensitive data that will be passed to SQL Server must be protected from SQL Server event leakage, then encrypt it before sending to the database. And then only decrypt at the database if it is required to query the data.

I can use Extended Events instead of SQL Trace. No Problem there. It is an improvement. However SQL Trace is not going away. That means if I want to roll out a schema or change scripts that contains sensitive information as clear text and have any concerns that someone might compromise the data by snooping on the SQL Server activity, I need to be sure to include 2 (additional ?) work items in the change requirements:

  1. Identify or develop suitable monitors to identify SQL Server event subsystem snoopers. 
  2. Code and test the change such that I am certain that no clear text leaks are exposed though any event of any event subsystem.

May as well jump in to a black hole…

SQL Server 2012 RTM extended events do not give up much info about the obfuscated text.

<event name=sp_statement_starting“…



SQL Trace – and so Profiler – is somewhat more informative to the snooper, revealing the operation that triggered the obfuscation.

<Column id=“1″ name=“TextData”>
– ‘OPEN SYMMETRIC KEY’ was found in the text of this event.
– The text has been replaced with this comment for security reasons.

Event Notification obfuscation leaves only “secrets” to the imagination/investigation, revealing more configuration information than obfuscated trace output. In Profiler I could see a key was opened. In the Event Notification I can also see that a password was used.

    DECRYPTION BY PASSWORD = ‘******’;  </TextData>

SQL Audit obfuscates about like Event Notifications. I would have expected that SQL Audit would work like an extended event. ? Go figure.

Statement               OPEN SYMMETRIC KEY ErrorKey

                DECRYPTION BY PASSWORD = ‘******’;

It not so important that monitoring and the audit trail rely upon extended events or trace events or notification events or Weekly Reader events or all of the above. It is important to consider all data bridges including each of the various SQL Server event streams if the plan is to detect sensitive data bridges and prevent leaks by not supplying unauthorized bridges with sensitive data.

Extended Event obfuscation in SQL Server 2012 seems to remain an undocumented feature so likely remains otherwise incomplete. The highest certainty will come from collection and detail analysis of trace and event session output in search of any secrets revealed in the event stream.

Whether using Extended Events or SQL Trace, or a similarly tedious study of any of the several events that do not directly map to user input; things like lock acquired/lock released or scan started/scan stopped, may be enough information to betray an application. Defense-in-depth demands that access to SQL Trace and Extended Events be strictly controlled and closely monitored in addition to solid cryptograpic protection.

Stored procedures can be trained to alert or abort if a white-listed Extended Event session is not active or is active but not found in a white-list. Traces are known by a system defined trace_Id instead of a friendly target name. The safest way to determine if you ought to white-list a trace or not is to analyze the definition: if it leaks, don’t white list it.

Once you have the name of an event session in T-SQL, it is only a little more work to identify the events being monitored. This is true for a trace or an event session, although the Extended Events catalog views and DMVs are nicer to work with using T-SQL.

SELECT s.name, t.execution_count, e.event_name 
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON s.address = t.event_session_address
JOIN sys.dm_xe_session_events AS e
ON t.event_session_address = e.event_session_address
WHERE s.Name = 'event session name'

for events target tables -vs- the functional APPLY join needed to read the he trace source

SELECT 'Default Trace' 
     , COUNT(*) AS execution_count
     , ISNULL(  e.name 
             , 'unmapped eventid ' 
             + CAST( trc.EventClass AS NVARCHAR(11) ) ) 
           , LEFT( [path]
                 , LEN( [path] ) 
                 - PATINDEX( '%\%', REVERSE( [path] ) ) )  
            + N'\log.trc' AS tracefile 
      FROM sys.traces
      WHERE is_default = 1 ) AS t
CROSS APPLY ::fn_trace_gettable( tracefile, DEFAULT) trc
LEFT JOIN sys.trace_events e 
ON e.trace_event_id = trc.EventClass
GROUP BY trc.EventClass, e.name;

Applications that rely upon T-SQL encryption and also carry a mandate to protect sensitive data from snoopers with elevated authority are in a tough spot. Especially these days as major holes are being poked in on-the-wire encryption. Adding complexity to the application – more complexity than has already been added with encryption is the only way I know of to achieve obfuscation.

A look at what it takes to submit a stored procedure using SSMS in SQLCMD mode will illustrate the difficulty. SQLCMD mode buys a few readable variable token literals ( e.g wrapped in $() ) in place of hard values. It also allows us to enter secrets into SQL Server that are unseen by any SQL Server event-ing subsystem.

DECLARE @BackupPhrase AS VARBINARY(8000); 



                                        , CAST( '$(master_DMK_BACKUP_PHRASE)' AS NVARCHAR(128) ) ) );
                                     , CAST( '$(master_DMK_ENCRYPTION_PHRASE)' AS NVARCHAR(128) ) ) );

EXEC $(EHA_SCHEMA).BackupDatabaseMasterKey @DbName = N'master'
                                         , @BackupPhrase = @BackupPhrase
                                         , @KeyPhrase = @KeyPhrase;

It is not really much more than an interesting aside, but all you have to do to keep SQLCMD variable assignment out of the trace output is to make sure the assignments are in a batch that does nothing. Put only the :setvar statements between two GO lines in the script. SQL Trace will always omit the no-op. Without this batch isolation, secrets may be captured in the trace output in clear text.

The CREATE SYMMETRIC KEY DDL password will be obfuscated. The assignment of cipher text values to the T-SQL variables will be obfuscated because the ENCRYPTYBYKEY function is called in the assignment. The passed phrases are binary cipher text.

DECLARE @BackupPhrase AS VARBINARY(8000); 

--*CREATE SYMMETRIC KEY------------------

--*OPEN SYMMETRIC KEY------------------

EXEC eha.BackupDatabaseMasterKey @DbName = N'master'
                                         , @BackupPhrase = @BackupPhrase
                                         , @KeyPhrase = @KeyPhrase;

And like this from the Extended Event subsystems perspective:



--*CREATE SYMMETRIC KEY------------------

--*OPEN SYMMETRIC KEY------------------

EXEC eha.BackupDatabaseMasterKey @DbName = N'master'
                                         , @BackupPhrase = @BackupPhrase
                                         , @KeyPhrase = @KeyPhrase;

Hey! What happened to <value>*encrypt—————–/~/—?

The symmetric key is a # temp key. That guarantees that the key will disappear when user connection is closed – and never existed before the user connection was opened. No values will be stored scrambled by this key. Its purpose is to obfuscate sensitive values passed to/between sql_modules. The encryption will be handed off to a permanent key dedicated to encrypting phrases in the database before the data is persisted.

Extended Events and SQL Trace produce identical batch obfuscation but produce different obfuscation at the statement level, highlighting the importance of evaluating all SQL Server event subsystems to prevent obfuscation leaks. 

Trying to manage obfuscation is a tad too obscure. I suspect most bottom liners will take the risk over the work. And the leaks will continue.

This entry was posted in Encryption Hierarchies, Monitoring, Secure Data. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.