Microsoft SQL Server Auditing: Part I

I’ve been set on an audit project for the next two-three months which, although it is very interesting and allows me to get on with something hands on, comes with a myriad of trials and tribulations. The colour really is purple this time too! Yes that's right; our initial milestone - the very security framework document the project will be based on - has to come right out of the bag with my Oracle counterpart... in the same security document.

So, following a couple of weeks sweating, betting and generally getting tied up by several internal polices, external benchmarks and PwC audit requirements, our security framework is in its final draft and with the stakeholders for sign off. Whilst that's being hung up to dry and ironed out, it’s time to think about the nitty gritty.

The estate is a mixed bag of SQL Server 2005 to 2008 R2 instances of varying editions, including Express, Standard and Enterprise, and with the project having no additional budget or intention to purchase extra kit, the challenge is on.

Naturally SQL Server Audit, possibly coupled with tidy Policy Base Management (PBM), would have been the order of the day. Indeed it may still be with the Enterprise editions out there, especially since there is a stake in the ground going forward with this edition and SQL 2012 (road-map for next project). You see, sadly pre-2012, SQL Server Audit is only available to the Enterprise edition. So what's available for the rest of the estate? Let's take a look:

- Login Auditing

- SQL Trace

- Triggers

- Default Trace

- C2 Audit Mode

- Common Criteria Compliance

- Login Auditing (auditing failed and successful logins) might be good to have, yet is not an all-encompassing audit solution as it is only tracking login successes and \ or failures to the Windows Application Log.

- SQL Trace (aka server-side tracing \ sp_sqltrace) is a good idea. The GUI version of which is SQL Profiler. There are forty or more audit events and more than hundred more other events to be traced. All results of the trace are stored to log files so it's a case of knowing what to trace and managing the log files.

- Triggers - great, let's fire an action on any DDL or DML event. Lots to configure here, highly customisable though and would have to be tailored per database.

- Default Trace - great for troubleshooting. We have an on-going server side trace happening under the hood for us when this is enabled, providing a lot of audit information to 20 perpetually churning log files, each of which are 200 Mb each. Maybe we can cipher them off?

- C2 Audit Mode - this is already becoming a legacy (replaced with Common Criteria Compliance) again uses SQL Trace to trace files. (We can use fn_trace_gettable to read trace logs by the way). We fortunately do not have to comply with C2 audit requirements on this project less deal with the log data this feature will generate (effectively tracking every move).

- Common Criteria Compliance - supersedes C2 Audit Mode – but let's stop there. Never mind the log data either, this is available in Enterprise only and yet another international auditing standard the project need not comply with.

"Well, what's in the security framework?" I hear. Quite a lot actually, certainly a good measure to start off a baseline audit. Service account and privileged user access needs to be tracked at all times, the rest is largely configuration options to reduce the surface area of attack. It's really all about covering off the basics and reducing the risk of brute force and DoS (Denial of Service) attacks - revoke access to public on certain extended procs; complex password policy, changing default database context, disabling sa, disabling xp_cmdshell...You get the idea.

The plan is to put together a server side trace for privileged login access (sysadmins and securityadmin roles) and build two sets of scripts to a) report on surface area configuration options and b) remediate all non-complying configuration options. The trace and config report will need to be fed into a temp db which can then be reported against with SSRS.

Let's see how this pans out in the design phase in Part II of this blog later this month... stay tuned!