News & Events

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!

Get in touch

For more information or to schedule a demo please contact us

Contact us

News

dsp winners at the UK Oracle User Group Awards 2017/18

dsp attended the UK Oracle User Group Partner of the Year Awards last week, and we came away with awards for both categories in which we were nominated

Read more
View All

Event

"Self Managing Databases: Fable, Fantasy or the Future"

Join us for lunch and to hear some war stories from staff and customers with experience of Oracle Database Cloud Service. We'll also understand more about the Autonomous Database and how Oracle will compete with AWS.

Read more
View All

Blog

DSP Nominated at the Oracle Partner Awards 2019

DSP is proud to have been nominated in the Oracle Partner Awards 2019 under the ‘IaaS and Paas Partner of the Year’ award!

Read more
View All