SQL Server Auditing

I have a N-Tiered Web Application built on ASP.Net and SQL Server. The application needs to audit CRUD operations that take place on the Database capturing – Who did what and when. It needs to log what user, did what operation at what time and what changes occurred as a result of the operation. I’m exploring some of the newer features in SQL Server to see if they solve this problem. SQL Server has builtin Audit features – maybe those will meet my needs so this post will explore SQL Server Auditing.

Previous Work:

I’ve created two audit systems in the past. Both worked sufficiently well, but due to time pressures and personal ignorance, both suffered from differing levels of problems in the areas of performance, usability and maintainability.

First Attempt

The first system had a schema roughly like this. There was a Log table that stored the user id, action and timestamp of the audit event and a details table that stored all the properties that were modified in that event.

Log
| User | Action | Time Stamp |

Log Details
| Action | Property | Old Value | New Value |

This was implemented with a CLR trigger on CREATE, UPDATE and DELETE. It looped through the fields for a given insert or update and compared them to the previous values. The CLR trigger was more performant than trying to do this with a CURSOR. It was a simple architecture and quick to implement.

The downsides were that the CLR stored procedure was rather difficult to deploy and maintain – it was unlike the rest of our C# and SQL infrastructure. More importantly, the logged results were usually at too low of level to be well understood. There needed to be an additional business layer on top of the results to make sense of it and this layer was not very performant or useable. For maintenance, every new table needed the trigger to be added whereas doing logging in C#, can be done in a single base class with reflection.

Second Attempt:

The second attempt moved all the logic to the C# Business tier. Audit events are generated from the middle tier in the application. This is good in that the auditing is done at the business tier. The limitation is that if a change is directly made to the database it is not audited. The existing system has a few scenarios that require direct access to the database through Stored Procedures or direct SQL queries to get the required performance. Also the existing system involves storing temporal data changes for related entities such as Group Membership – Ie, if you want to find out ‘what did group X do in 2008’, you need to know who was in that group during that time period. There’s a fair bit of data needed to support this operation. If possible I would like to offload all of this data and logic to a separate Auditing System.

New System:

If possible I would like to offload the auditing logic and storage to a separate system. This is because my Dataset being audited is relatively small, but the number of audit events is relatively large given the update-frequency and that I require auditing of select statements as well.

The database I’m working with is relatively complex – probably 200-500 tables in the end. The tables represent 100+ entities within the system. Given this complexity a generic system is definitely preferred over manually logging each entity at the business tier. This generic system certainly can live at the business tier, but for now I’m exploring the feasibility of doing it at the database tier.

Arguably, an audit log using off-the-shelf SQL Server auditing functionality is more ‘trustworthy” or reliable than a roll-your-own solution. We develop at a high level of quality in general so I don’t see this as an overly strong motivator.

MSDN Resarch

To quite MSDN – “SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files. ”

I’m suspicious that this is only referring to system events such as “Rebuilding indices or Query Deadlocks” and not DML events. Further reading and this link makes me somewhat optimistic. It appears the “c2 audit mode” options records all queries in a rolling log file – perhaps similar functionality exists for inserts and updates.

So Auditing looks like it can be done. It can log all operations on a table to a binary file or the Windows Event Log. I have a hunch that this will log the raw SQL commands and not necessarily the before and after values of the data that was modified.

Well two unfortunate results:

 

1. SQL Server Enterprise edition is required to use ‘Granular’ Event Logging. Apparently ‘Basic’ logging is supported without enterprise edition, but I have not been able to figure out what Basic logging is. Luckily this post here has an example of the output.

Screen Shot 2013-02-09 at 3.52.36 PM

2. It only logs the SQL statements executed and not the affected data. This isn’t a big surprise, but it means SQL Server auditing will not meet my needs. Converting from arbitrary SQL statements to higher-level business entities is too complicated to be feasible.

Summary:

SQL Server auditing does not meet my needs. It looks best suited for applications where more logic is stored in the database. If an application used SQL users and stored procedures the builtin audit features would provide far more value. Next up I’ll look into SQL Change Tracking – I think it’s better suited for what I need.

Advertisements
This entry was posted in Architecture and tagged . Bookmark the permalink.

2 Responses to SQL Server Auditing

  1. I also have an ASP.NET MVC C# CRUD app working against an MS SQL database.

    If you haven’t found a solution, may I highly suggest AutoAudit, written by the author of the SQL bible?
    http://autoaudit.codeplex.com/

    It’s a single script which generates the necessary triggers, stored procedures, and tables (2) to turn auditing on and off whichever table you like. You enable auditing on a table by calling an SP.
    EXEX pAutoAudit ‘MyDataBase’ , ‘MyTable’

    The solution is perfect for databases still in the design phase because there is a database trigger that also keeps the auditing in sync when you add or remove tables or columns. Another advantage is the simplicity of keeping all audit logs in a single table. No need for doubling your tables and maintenance with *_audit tables all over the place.

    This may not be best for your tables with high turnover, but for logging user changes to standing tables without alot of turnover, its the bees knees.

    If your SQL server is so loaded that it can’t incur the penalty associated with CRUD+triggers, then perhaps look at an asynchronous Service Broker to offload the auditing to another server.

  2. codealoc says:

    Oh cool. Thanks a lot for the link. I had not come across that – I wish I had come across that much sooner!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s