Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

The lowdown on SQL Server auditing tools

Learn about the various components of SQL Server auditing tools such as server and database audit specifications as well as action groups.

When Microsoft released SQL Server 2008, they introduced SQL Server Audit, which are comprehensive SQL Server auditing tools that address many of the limitations of auditing capabilities in earlier versions of SQL Server, when DBAs had to rely on SQL Trace and other tools. But SQL Server Audit is now native to SQL Server and, as such, is integrated into SQL Server Management Studio (SSMS), providing a simple interface for implementing auditing at a fine-grained level so you can target specific objects, actions and principals.

SQL Server Audit tracks and logs events that occur in the database engine. It can record events at the server level or individual database level, although the latter is possible only in the SQL Server Enterprise and Developer editions. SQL Server Audit provides the SQL Server auditing tools necessary to set up, enable, store and view event data, and is fully manageable not only through SSMS but also T-SQL and Server Management Objects (SMO). Unlike SQL Trace, which is as much about performance monitoring as it is event tracking, SQL Server Audit focuses only on auditing in order to deliver the security, performance and manageability necessary to ensure comprehensive auditing.

SQL Server Audit components

Figure 1. SQL Server Audit includes server audit objects and specification objects.

Most of the work SQL Server Audit does happens behind the scenes. However, in order to implement auditing, you should be familiar with the objects that must be configured and enabled in order to audit events. Figure 1, below, shows the components of the SQL Server auditing tools that participate in the auditing process. Note that some of those components are specific to SQL Server Audit and others are part of the SQL Server or Windows operating system environments.

At the core of SQL Server Audit is the server audit, a server-level object that lets you configure settings applicable to the audit as a whole, such as how the server should respond to an audit failure. The most important of these settings defines your target, the destination where you want to store the event data. You have three choices for your target: the Windows Security event log, the Windows Application event log or binary files stored in a specified location.

SQL Server documentation sometimes refers to a server audit as a SQL Server audit or simply as an audit, which is how server audits are referenced in Object Explorer in SSMS. What makes this more confusing is that their documentation also refers to a configured collection of SQL Server Audit objects as an audit. For example, in the figure above, the components enclosed in the blue box with the dotted border would be considered an audit. For our purposes here, we use the term to reference the entire package.

Once you've configured your server audit, you can add a server audit specification. The specification defines which server-level events to include in your audit. You can define only one server audit specification per server audit, and that specification must point to that server audit. In addition, when you configure the specification with your SQL Server auditing tools, you select one or more action groups, which are each associated with a collection of events. For example, if you want to monitor which principals tried to log on to a SQL Server instance and failed, you can add the FAILED_LOGIN_GROUP action group to your specification.

In addition to the server audit specification, you can add one or more database audit specifications. Not surprisingly, this is specific to a database. You can create only one database audit specification per database per server audit. For example, Figure 1 includes two database audit specifications, which means two databases will be audited, with one specification associated for each of them.

As with the server audit specification, you can select one or more action groups to associate with the database audit specification. Each group points to a collection of related events. In addition, database audit specifications also let you select individual actions (such as update or delete) on specific database objects (such as tables or views) and specific principals (such as a SQL Server login or database role). For example, you can monitor when the sysadmin principal runs a DELETE statement to remove data from the Persons table in the AdventureWorks2012 database.

For both server and database audit specifications, SQL Server also supports the AUDIT_CHANGE_GROUP, which lets you audit events related specifically to the auditing process itself. Note, however, that SQL Server Audit, by default, already monitors some auditing-related events.

One other component of SQL Server auditing tools that you'll notice in Figure 1 is SQL Server Extended Events. These are the actual events that the actions and action groups call. Extended Events provide a built-in event handling architecture for capturing events specific to SQL Server. The events occur at execution points within the SQL Server program. Extended Events makes it possible to capture the events for use in tools such as SQL Server Audit and SQL Trace.

Robert Sheldon
is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems and business intelligence design and implementation. You can find more information at http://www.rhsheldon.com.

Next Steps

SQL Server auditing features and tools have come a long way.

Dig Deeper on Microsoft SQL Server Tools and Utilities