SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Can Auditing Fail?

By Steve Jones,

This editorial was originally published on May 5, 2015. It is being republished as Steve is at SQL in the City in Chicago.

I saw someone's list of requirements for a SQL Server auditing system recently that included many of the standard items most of us would want. DDL changes should be captured, along with configuration alterations. The performance impact should be low, and all versions of SQL Server must be supported. However, there was one requirement that surprised me. This person noted that the auditing must be asynchronous and the application should not be dependent on the auditing. If auditing failed, the application shouldn't have any of its transactions limited or rolled back.

I'm sure there are use cases where this is important, and where the auditing might not be critical, but the auditing data is available for informational purposes to troubleshoot any issues that relate to the database. However in many places where auditors review information, or the data is part of a legal record, the auditing cannot fail. If it does, then the application needs to stop working. If an audit is truly an audit of activity, then all activity must be audited.

The C2 audit mode in SQL Server has been deprecated. Probably for multiple reasons, but It did seem that the idea that a failure in auditing could stop the database wasn't a setting many people were willing to implement. To me, this means that auditing isn't as important as having the system continue to process data. If that's the case, then is auditing that important?

I know auditing data can be overwhelming. I know that the management of audit data, including archival, is complex. I also know that most of the data isn't very useful and will never be examined. However when we need audit data, we really, really, need accurate audit data. I really wish that Microsoft would integrate auditing better into SQL Server to ensure the data can be easily managed, compressed, and archived in an automated fashion.

Total article views: 123 | Views in the last 30 days: 2
Related Articles

Auditing the password changes using LOGIN_CHANGE_PASSWORD_GROUP Server

Auditing the password changes using LOGIN_CHANGE_PASSWORD_GROUP Server Level Audit Group.


Auditing Your SQL Server - Part 1

A new series that examines how you audit activity in SQL Server. The first part looks at simple data...


SQL Server Auditing - Part 1

Our SQL Server Security expert, Brian Kelley, brings us the first part of a new series on auditing. ...


The Auditing Poll

Is auditing in use in your applications? Steve Jones wants to know as he thinks it will be more impo...


Making Good Use of SQL Server Auditing

SQL Server Auditing is a powerful out-of-the box toolset that captures auditing information and writ...