Release History
Version # Date Description Download
1.0 04/22/2009 Includes the initial audit framework including DB, SSIS, and SSRS Source Code-15949
1.1 04/22/2009 Includes Restart SQL Audit Policy and Job Source Code-15950
1.1p1 02/08/2012 Updated V2 scripts (add on top of existing code) Patch-340111
1.5 TBD _Will include features such as Report dashboards and an OLAP database

IMPORTANT: To download these files, click on the Source Code link which will give you the source code view; from there, click on the Download link. Do NOT use the download link on the right.


Authors
Denny Lee, Ayad Shammout; with contributions from Andy Roberts


Project Description
With SQL Server 2008, there is a powerful yet lightweight method to audit a SQL Server instance. But to manage and view the audits of your entire SQL Server environment, we have created the Centralized Auditing Framework that will parse, load, and report all of your audit logs.

sqlaudit_diagram.png

From a high-level perspective, the architecture to audit sensitive operations for your SQL Server environment will be to:
  • Turn on auditing on your various database and servers; you can find more information at Understanding SQL Server Audit
  • Have these audit logs go to one centralized location instead of a local folder (this is indicated by the Log Folder in the above figure)
  • Included in the Centralized Audit Framework is an SSIS package that will
    • Parse through all of these audit log files
    • Populate a centralized database (also included) with audit dimension data (class, type, server names, etc.) and audit fact data (audit events separated by category of server, database, DDL, and DML actions).
  • Once the data is loaded, a nightly job will process the data to product report tables
  • These report tables can be viewed by SSRS reports like the Audit Server Actions report below

sqlaudit_serveractions.png


More Information on SQL Audit and Compliance

Last edited Feb 8, 2012 at 2:22 PM by dennyglee, version 9

Comments

No comments yet.