In previous posts we discussed ways to identify and secure data that resides in a SQL server database.  Encrypting and masking data are good options for preventing access by unauthorized users, but how do you monitor against access by Insider threats and/or compromised accounts?  One way to do this is with auditing SQL.  This however comes with a couple challenges.

• DBA’s and developers are hesitant to audit data because of the potential overhead.

• Querying out of System Tables and Cache can be intensive, and somewhat complicated for anyone but SQL guru’s to reverse engineer.

• Basic SQL Server Auditing is limited to Logon Events (below)

But there is a very reasonable option that is often overlooked, an option that if deliberately used will not introduce performance issues and will likely flow into Security tools via an existing windows events stream.  The option is Database Level Auditing.  Put simply you create an Audit in SQL and configure what you want to audit. The audit can be limited down to a table and a user, which is a great option if you want to monitor access to a particularly sensitive table or potentially monitor a Honey Pot Account.  This option can be configured through the GUI or with T-SQL.

Then its as simple as Selecting the Events, Objects and User combination you wish to audit. * Note you must reference a SQL Server Audit and direct it to an output location, my preference is one of the Windows Event Logs.

Corresponding T-SQL

CREATE DATABASE AUDIT SPECIFICATION [SQL_Audit0307]

FOR SERVER AUDIT [SQL_Audit0307]

ADD (SELECT ON OBJECT::[dbo].[CustomerInfo] BY [PrivilegedReader])

WITH (STATE = ON)

Let’s see what it looks like when we execute a query against our sensitive data table. Note that the User, SQL Statement as well as any defined data labels are returned.  This information can be particularly useful for monitoring and demonstrating compliance. 

Pros:

• Simple Configuration

• No Risk of breaking application code

• Minimally intrusive if used carefully

• Helpful for detecting potential accidental and malicious data violations

Cons:

• Use deliberately to avoid performance issues and SEIM ingest problems

• Be aware that the text in the SQL Statement will be written to the log, be careful not to capture or expose sensitive data in log files.  Fortunately input variables parameterized via stored procedures are obfuscated.