Share via

SQL Server 2012 – SQL Audit

  • Article

Overview

In this post I will highlight SQL Audit and show you a real world example of how to audit modifications to custom SharePoint databases.  The SQL Audit feature in SQL Server 2012 has several enhancements that make it more efficient and easier for organizations to meet their data compliance policies and regulations.

SQL Audit in SQL Server 2012

SQL Audit allows the database administrator to track and log server level events as well as database level events.  Audited events can be written to the Windows event logs or to binary audit files which can later be analyzed using T-SQL. 

Audits consist of a server level audit which defines where the audit log records will be written.  Database and server level audits can be filtered on particular users.  SQL Audit executes asynchronously so there is no need to worry about auditing holding up user transactions.
Scenario

In this scenario a customer has a custom SharePoint database with a table used to store data collected from a survey.  The data in this table is sensitive to the organization.  To adhere to security regulations the organization must track and log all deletions from this table and information about who deleted the records.  Additionally the audit needs to be filtered so that only deletions by users who are members of the db_datareader and db_datawriter role are tracked and logged. 

In this scenario it could be authorization creep or someone who is supposed to have access to delete but either way the customer would like to track and log this activity.

Step 1 - Creating the Audit

I have configured an audit in SQL Server Management Studio.  This audit will write to a binary file on the file system with no maximum file size.  Log file size options and other various settings can be configured.  A new option for DBA’s in the “On Audit Log Failure” section allows more flexibility and prevents the server from shutting down when auditing cannot be performed for some reason.  Another new option which I really like is the ability to filter the audit on a particular database or user name and many other filters.

In the screen shot below the audit has been configured to continue if the audit log fails for any reason.  The target of the audit is a binary file located at C:\SQL Audit with an unlimited file size.  

NOTE: Administrators must ensure that audit destination has enough storage to handle the logging of events.  The file destination can be a network share UNC path \FileShare\.

Step 2 - Create the Server Audit Specification

By running the following script a Schema Change audit group will be setup:

Step 3 - Create the Database Audit Specification

By running the following script a database audit will be setup to track DELETE statements on the dbo.SurveyData table by anyone who is a member of the db_datawriter role.

Step 4 – Verify auditing

To verify auditing is setup correctly and is successfully tracking the DELETE statements on the dbo.SurveyData table I will remove a record from the table and then analyze the SQL Audit logs.

  • Check the file system to ensure that the Audit log file has been created:

  • The dbo.SurveyData table is defined as shown below:

 

  • The dbo.SurveyData table has been pre-loaded as shown below:

  • The following T-SQL statement has been run by a user CONTOSO\TestUser who is a member of the db_datawrite role in the customer SharePoint content database:

NOTE: By running this statement I am simulating a user removing a single record from the dbo.SurveyData table.

  • Analyze the SQL audit logs to see if the DELETE statement is being logged correctly:

NOTE: Using the fn_get_audit_file function a DBA can analyze logs from the binary file just as they would do with a SQL trace file.  Using this function the information in the binary audit log file can be extracted and moved off into a database for archival purposes.

Summary

SQL Audit in SQL Server 2012 provides a robust and flexible solution that enables organizations to track and log changes to individual tables by certain users and roles.  SQL Audit is easy to setup and administer.  With enhancements like Max Rollover Files, Max File Size and Filter DBA’s now have many more options to make auditing more efficient and useful.