The graphic above describes the unique approach we've taken with Microsoft SQL Server 2008 Enterprise Edition to implement a federated audit system that consolidates all our audit logs from multiple SQL Servers and non-SQL sources into one place. We use a SQL Server Integration Service (SSIS) package every 15 minutes to fetch through the Audit files and upload the data to Central SQL Audit DB Repository to capture:
i. Server level: all login in/out/failed events, and server configuration changes
ii. Database level: Create/Alter/Drop db events
iii. Object level: Create/Alter/Drop object events
iv. Data level: Insert/Update/delete and select events (we didn�t enable Select events in phase I)
Then, we use SQL Reports to query and view the audited data (i.e. who made this change, who modified a table, who insert/update/del a record)
Our next step is to process all audit data with SQL Server Analysis Services, create cubes to analyze the collected data, and build reports/alerts based on threshold (e.g. on average there are 10,000 logins/day, an alert will raise if we exceed the threshold)
Microsoft will be releasing soon a Compliance SDK on Security and Auditing based on their collaboration with BIDMC's SQL team. The SDK will be available for download so that other companies can use our Auditing solution as a model.
Creating an enterprise tool for consolidated storage, reporting and alerting of all application audit data - that's cool!