In my organization, one of my jobs is auditing our database environment.  I’ve been tasked with this responsibility for several years now, and it hasn’t always been easy.  In fact, I used to despise the entire process.  Why?  Because I wasn’t using the right tool for the job.  I didn’t know what options were available to me.  Granted, I started out in SQL Server 2000, where there weren’t a whole lot of choices to begin with.

Over the years, I’ve taken a particular interest in auditing options available in SQL Server, mainly with the goal of making that part of my job easier.  True, I probably could have gone to my boss at any point and said “hey, we should get a third-party auditing tool.”  But the fact is, I get a kick out of seeing just what I can do with each tool.  How I can spy on watch over my users and developers.  Making the most of the tools I have at my disposal already.  And these days, our audits are a piece of cake.

Why audit?

There are a number of reasons why you might need to implement auditing in SQL Server.  Maybe your company is bringing in an outside firm to perform security audits.  Or you might even be required by law to perform such auditing.  From a development perspective, auditing DDL changes can supplement a change management system.  Or it can help you answer the question, “what changed?” that will inevitably be directed at you when the poop hits the fan.  Auditing can tell you who’s accessing that sensitive data, or help you figure out what a particular login is being used for.

What can you audit?

So what exactly can you audit in SQL Server?  Just about anything.  For example:

• DDL changes: create, alter, drop, truncate
• Logins: all logins, failed logins, logins by sysadmins
• When Agent jobs are created, removed, or changed
• Who is accessing sensitive data or procedures
• Who is trying to access sensitive data or procedures
• Changes to a user’s or login’s privileges
• Server or database configuration changes
• The use of deprecated features

The thing is, almost everything that happens in SQL Server generates an event.  A user logging in generates an event, as does that user issuing a query.  Any locks that occur while that query executes generate events.  Any waits, any disk space allocations, any object creations: they all generate events.   And if it generates an event, chances are good that you can audit it in some way.

What are your requirements?

Ok, so you’ve decided to audit your SQL Server database.  Or maybe you’ve been told to.  Either way, the first thing you need to do is figure out your requirements.  Taking time to plan out exactly what you need will save yourself tons of time and frustration later.  Trust me.

What do you want to audit?  The most important thing you need to decide is exactly what events you want to audit.  Is this a DDL audit?  Is it a security audit?  Some auditing tools are better suited to handle tracking specific events, so depending on what you need to record, this could rule out a solution completely.

What data do you want to collect?  If you’re auditing logins, you’ll obviously want to know the login name, and most likely the date and time of the login.  But do you also need to know the application?  What about the network login of the user or the client’s hostname?  If you’re auditing DDL changes, it would definitely be useful to capture the SQL statement issued.

Where do you want to run this audit?  Are you only planning on auditing a single database on a single instance, or is this going to be a system-wide thing?  If you’re planning on auditing many environments, you’ll want something that’s easy to implement and maintain; maybe something you can manage centrally.  The SQL version and edition of the audit target also matter.

Where do you want the output to go?  Should the output be written to a file or would you prefer a database table?  If you’re auditing several databases/instances, should they all write to separate outputs or a single repository?  Should DDL audit output commingle with security audit output?  Who should have access to the output?  And how long do you want to retain it?

How will the audit output be processed?  Will you be reporting on the output?  Will you need to search the output for specific events?  Will you need to compare or search output from various audits?

What’s your budget?  SQL Server has several free built-in tools that you can use to audit your database, but there are also a number of third-party tools available.  Of course, these products come at a price, and that price generally goes up in proportion to the number of systems you want to audit.

Additional considerations

Once you’re gotten your requirements firmed up, you can start using them to select an audit tool.  In SQL Server, you have several options at your disposal, with each offering its own set of pros and cons.  When picking out a solution, obviously you want to ensure it meets those requirements, but there are a few more things you’ll want to consider.

Will it impact normal processing?  Ideally you want a solution that will have minimal or no impact on the day-to-day performance of your SQL environment.

How tamper-proof is it?  This is especially important when it comes to security-related auditing.  You want to know that someone can’t mess with your audit to avoid having certain events recorded.

How easy is it to implement/maintain?  The easier your audit process is to implement and use, the less painful auditing will be.  Generally speaking, if you dread the whole audit process, you’re probably not using the right tool.

How granular is it?  Can you filter out certain events or objects?  This is something that’s especially important to me.  I do DDL auditing on a database where certain objects are routinely dropped and recreated by the application.  I don’t care about those objects and I don’t want them in the audit report.  I also don’t want things like index maintenance showing up.  So the ability to exclude objects or events is something I look for.

Coming up

Now that you know what you need, it’s time to start test driving some solutions.  In the days (ok maybe weeks) to come, I’ll be discussing several of your options for auditing events in SQL Server.  I’ll talk about how they work, what their pros and cons are, and hopefully introduce you to some new ideas for implementing and using them.  Auditing is necessary, but it doesn’t have to be boring.  Good stuff ahead, people.