Auditing Tools in SQL Server 2008 and SQL Server 2008 R2
This is the third 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.  The second article discussed the auditing log data best practices, both what event data to include in your audit logs and the need to protect your audit log systems and data from tampering.  This article will look at the various tools that we can use for auditing and the pros and cons of various tools that SQL Server 2008 provides to us administrators and developers to enable auditing compliance.

SQL Server contains many tools that can be used for various types and levels of auditing:

 

Auditing Feature

Version Introduced

Advantages

Disadvantages
DML Triggers <= SQL 2000
  • Available in all editions and versions of SQL Server
  • Allow for fine-grained auditing
  • Can use SQL Server Reporting Services to easily report
    • High overhead, auditing is part of transaction
    • Implementation cost is high, need to implement separate trigger for each table
    • Can only be used for INSERTs, UPDATEs, DELETEs
    • Low security: can be disabled by db_owner to bypass
SQL Server Profiler <= SQL 2000
  • Available in Standard Edition
  • Allow for fine-grained auditing
  • Can audit all types of events
    • Low-medium performance overhead with server processed events (recommended for auditing)
    • Need to run a tool outside of SQL Server
    • Risk of database activity without auditing running
    • Difficulty reporting depending on audit log location (SQL table vs. trace file)
SQL Server Server-Side Tracing <= SQL 2000
  • Available in Standard Edition
  • Allow for fine-grained auditing
  • Can audit all types of events
  • Much more difficult to disable than DML triggers or SQL Server Profiler and disabling can be auditing
    • Low-medium performance overhead
    • Implementation cost is high to setup,configure,and make sure it is running all the time
    • Difficulty reporting depending on audit log location (SQL table vs. trace file)
C2 Audit Mode SQL 2000
  • Easy to configure
  • Meets federal C2-level security audit specification
  • Lots of detail in the audit log
  • If auditing cannot occur, will stop server
  • Can log reads as well as changes
    • All ornothing,logs every activity on the server
    • Can easily become too much data
    • Extra work required to reportfromdata
    • Many extend stored procedures do not work with C2 auditing enabled
    • Includes more than just user access data
DDL Triggers SQL 2005
  • Can audit for schema changes
  • Fairly easy to implement, especially with event groups
  • Disabling is audited

 
  • High overhead, auditing is part of transaction
  • Can only used for DDL statements
  • Can be disabledwithout tomuch trouble
Event Notifications SQL 2005 (SP1 for a security fix)
  • Allows for fine-grained auditing
  • Low overhead, auditing is asynchronous, but:
  • Highly reliable, using the SQL Server Service Broker
  • Most secure auditing mechanismpossible if configured correctly and disablingis logged
  • Supports all editions of SQL 2005 and SQL 2008/R2, only requiring one Standard or Enterprise edition for audit log server.
  • Fully customizable reporting possible using SSRS
    • Can be difficult to setup, especially in the more secure scenarios
SQL Server Audit SQL 2008
  • Easy to configure
  • Very secure
  • Choice of log locations (file – easily queried and/or placed in SQL table, or either Windows Application log or the Security Log
  • Can integrate almost seamlessly with other auditing measurement and reporting systems that collect from the Windows Security Log
  • Low overhead
    • Possibly not as secure as some Event Notification setups
    • Requires SQL Server 2008/R2 Enterprise edition (or greater). Won’t work with Standard or less or with SQL Server 2005.

I hope you found this article looking at the available auditing tools in SQL Server 2008 interesting and useful.  In the next articles in this series, we will look at the two most powerful, secure, and flexible auditing methods in SQL Server 2008, SQL Server Audit and Event Notifications.  Following those we will look any changes to auditing that SQL 2012 provides us.

Has this helped you and what do you think?