Better Auditing Capabilities with SQL Server 2008

Thursday Jan 7th 2010 by DatabaseJournal.com Staff

Auditing your SQL Server Environment is the key to keeping your data secure. Learn more about audit enhancements available with SQL Server 2008.

The key to auditing your data is having the knowledge of who is accessing your data, or who is trying to break-in and steal your data.    Break-ins or attempted break-ins are not the only thing you need to worry about tracking.  You also need to monitor inappropriate use by those individuals that do have legitimate access to your data.  Another area to be concerned about is to make sure your administration staff are not making inappropriate configuration changes. 


SQL Server 2008 Enterprise addition now includes a robust audit mechanism built into the database engine.  The new SQL Server Audit capabilities provide more flexibility and granularity to what you can audit.  SQL Server Audit was design with the following things in mind:


·         Audit feature, and its objects, must be truly secure

·         Performance impact must be minimized

·         Audit feature must be easy to manage

·         Audit-centric questions must be easy to answer


The audit functions within SQL Server 2008 are fully manageable from within SQL Server Management Studio, through SQL Management Objects (SMO) and/or Transact-SQL DDL.  This allows you the capability to programmatically manage the auditing functionality using Transact-SQL scripts or using SMO.   


Auditing of SQL Server is made up of three main audit objects.  There is the “Server Audit” object, which defines the target of the audit, which can be a file, the Window Application or Security Log.  The “Server Audit Specification” object describes what needs to be audited at the server level.  The “Database Audit Specification” object identifies what needs to be audited at the database level.   You use all of these to provide your set of audit specifications.   This white paper dives in and describes in detail all of these different objects.  Along with this, there is a technical architecture discussion on auditing, that touches on permissions and the performance consideration of using the new auditing aspect of SQL Server 2008. 


The new auditing feature of SQL Server 2008 provides robust and comprehensive auditing capabilities for an enterprise.  These new audit features perform much better than the older SQL Trace method.  If you have not been doing auditing or have been using the audit features of older versions you owe it to yourself to read this white paper to better understand how SQL Server 2008 can meet your audit requirements.   


Mobile Site | Full Site