Microsoft Training Blog

SQL: Auditing Event Data While Protecting Auditing Systems and Data

This is the second article in a series discussing database auditing. The first article discussed the need to audit from various governmental mandates and best practices of auditing just the data you need to meet requirements- refresh your memory on SQL Server Database Auditing.  This article will continue discussing auditing best practices, including what data needs to exist in your audit logs and the need to protect your audit log data and auditing systems from being disabled or removed.

Auditing Event Data

First of all I need to say that auditing compliance is more than just having systems in place to track what users are doing with data.  It includes many other aspects besides just the monitoring systems.  All the security mechanisms are important but even with the best, state-of-the-art auditing system, if someone can walk in and walk out with your server or backups, you will be out of compliance with several of the regulations.  I could go on and talk about all the other security systems that need to be in place, but suffice it to say auditing of data access is just one part of auditing compliance and let us agree that we will all implement those systems. 

Returning to auditing (aka monitoring systems), a key part of any such system is a measuring and reporting mechanism so that the auditing data can efficiently be used.  While those are mainly outside the scope of our discussion of generating the auditing data, I will mention the ease (or difficulty!) of implementing those systems when we look at the various tools SQL Server 2008/R2 provides.

Any good auditing system will include, at the very least, the following information:

• Who accessed the data?
• What type of access was it?
• New data or change, delete, or read of data – could include systems change like permissions change or new users/logins
• What application accessed the data?
• Where was the access from? (What network and/or what computer?)
• What was the statement or command that accessed the data?
• Was it successful and what data accessed (if successful)?

Protecting Audit Event Data and Auditing Systems

Protecting your auditing data systems and audit data is a very important aspect of a compliant auditing solution.  You might otherwise have a very good auditing system, but if it can be disabled or removed easily, then in total, the system is weak.  Various techniques are used by system hackers (both external or internal) to cover their tracks.  If they can disable auditing systems in some way, then they can then do what they want without auditing occurring. 

Methods include:

• Straight out disabling, and if they can do so without us knowing so much the better,
• Filling up the auditing log location
• Disabling certain network traffic to prevent sending auditing records to remote servers
• Clearing the log at the end
• Generating large amounts of data to “push” their records out of rolling window auditing solutions.  

A good audit system will try to account for these types of attacks by:

• Providing ample storage for auditing events
• Having in place notifications for unusually high volumes of activity
• Using tools and methods that are difficult to disable
• Shutting down the server, if unable to log to the event location. (When government regulations are involved, denial-of-service can be better than insufficient logging).  

As a last resort, your auditing solution should include logging of disable events or clearing of event logs.

In our next article we will look at various tools that SQL Server 2008/R2 provides to us for auditing, including what methods are more secure with the above listed best practices in mind.

SQL Server training at QuickStart is available!

Thanks!

Steven Allen, QuickStart Intelligence, Systems Engineer