Auditing Tools in SQL Server 2008 and SQL Server 2008 R2
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
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.
