Cool Technology of the Week

At BIDMC and other Caregroup hospitals, auditing is a critical component of HIPAA compliance and ensuring patient privacy. We currently have 1 billion rows of audit data from 146 mission critical clinical applications. Our comprehensive audits of every clinical lookup yield 300,000 – 500,000 transactions per day. HIPAA requires an audit system to record who is looking up what, where and why. We need to keep these audit logs for 20 years.

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!