Auditing Database Servers with SQL Server-A Primer

I’m not a betting man but if I were, I’d bet your typical SQL or BI Developer have never used the auditing features on SQL Server. Don’t ask them what it is as that’s too easy (term kindda gives it away, right?), just ask them to show you where it is on your servers. My experience is, most developers assume this critical functions rests with and have been implemented by the DBAs but regrettably, that is not always the case (actually, ask your DBAs the same questions while you’re at it). And if it has, DBAs are generally far too removed from the business to have any knowledge of what is important from a business perspective.

If it’s not there, it could also be because there are, as always alternatives; we for one used DDL triggers (our main servers predates auditing on SQL which came out in the 2008 version) and others may well use any one of the other alternative, like traces. Or, it may not be there as it was not deemed useful or necessary. But auditing as a feature came out for a good reason, more features, less overheads, better performance and, easier to use.
What follows is a very brief intro and overview of this critical function- auditing, even from a purely technical perspective can a massive topic with many features, options and approaches, all intertwined with other features. It is intended only to show you how easy it is to get started and hopefully have more people using this great tool-it’s certainly more productive than denying database access to everybody 🙂

We won’t cover the important issues of audit strategy but a sensible guideline is to limit auditing to the exact set of actions of interests and nothing more. We also won’t have time to cover the critical issue of permissions but the following (big red) warning from the Microsoft site serves as a crucial point/reminder before any implementation. Just plan and exercise common sense.

Permissions

Auditing on SQL Server encompasses the following components:

  • The Server Audit
  • The Server Audit Specification
  • The Database Audit Specification

Together, these three components allow you to monitor your server from two different aspects: at the server level and the database level. Database level audits are only available in SQL Server 2012’s Enterprise, DataCenter, and Developer Editions. At the server level, you can:

  • Monitor things that impact the server
  • Monitor changes across the database
  • Monitor changes to schemas to database

At the database lever, you can monitor actions that:

  • Are specific to a database, objects or schema
  • Are specific to a principal (user)
  • Are specific actions (Updates, Deletes, Select, Execute, Create, etc)

As noted, there are many configuration options available. One of the more important one is the audit destination; where things are logged to and these include, files, Windows Application logs or the Windows Security logs. For this quick demo, we’ll use a file to keep things simple. Just ensure SQL have access to that file otherwise things won’t work.

As usual, while we’ll use code for our demo, the same thing can be performed using a GUI-just google it for those more comfortable with that approach. The source code can be downloaded here.

 

Let’s start, firstly with auditing our server.

We first need to create the Server Audit and enable it. Recall we’ll be using a file for our log (note the Filepath in red).

Server

We then need to create the Server Audit Specifications. It is here where we define the actions of interest to log. I’ve listed a few actions here to show the syntax but if you want to see what’s available, this is documented here on the Microsoft site.

For the action or event of interest to be captured, we’ll create a new user (shortly) and give him ‘System Admin’ permissions. Our server audit should capture this creation/grant.

Spec

Before we do that, lets go to our Object Explorer where we can see the objects we just created (and enabled) from above. Note normal users (non administrators) won’t be able to see this.

ObjectExplorer

Now create the new user and make him a System Administrator (SA) so our logs can capture this event.

create

It is now time to look at the logs and see what we’ve captured. As expected, it’s logged the statements we used to create our user, the granting of the SA privileges, time of execution(s), person who did this together with file location (amongst many things).

Log1

Lets test it again but this time, as someone else other than me that created the user. We’ll log in using the ‘sa’ account to create the user. Run the query on the log again, and you’ll see that this time, it was ‘sa’ and not ‘sammy1188’ that ran the SQL statements.

Log2

Ok, this time, we’ll audit our database. Like before, we’ll firstly need to create the server audit and enable it.

db

We then need to create the Database Audit Specification and enable that. Note this is created on the database you want audited, in our case, AdventureWorks2012. Also note I am only targeting one user, [DESKTOP-OUHUSS9\raymo]. This is to show how we can target not only actions (Select, insert, update, etc) but also specific users and groups of users such as dbo (commented out in green). For a complete list of actions and action groups, refer to Microsoft docs noted earlier.

db3

With the database audit enable, we’ll run a select statement but firstly, under someone other than [DESKTOP-OUHUSS9\raymo], the user we’re targeting. Notice the audit didn’t log the actions of this user (me). Recall our code was targeting ‘raymo’ and no one else on this table.

sam

Now have the user ‘DESKTOP-OUHUSS9\raymo’ run some queries on our table that’s being audited.

RaySelect

Now let’s look at our logs again. As expected, we have an entry for each time ‘DESKTOP-OUHUSS9\raymo’ has queried this table (3 times). Also note as a normal user, he will not be able to run this query on the logs. Pretty useful and simple hey?

Audit2

This is what our logs look like at the source, it can only be viewed by SSMS.

logs

An alternative approach to running a SQL query is to view it in the Log File Viewer. Just go to Security folder>Audits folder> the Actual Audit itself within SSMS. It’ll look like this below. Again, you’ll need to be an administrator or have been explicitly granted permission to see this, otherwise the folder will be empty.

Log File Viewer

And finally, some of the common systems views to help you manage the audits. You’ll see the complete list at the Microsoft page on the links provided earlier.

views

So there you have it, a primer into auditing in SQL Server. You may or may not need it immediately but one thing I am absolutely certain of is that over the longer term, there will come a day when something unexpected happens on your server and your managers asks or you’d wish you had something in place to tell you exactly who and when if not why. We have only scratched the surface here, auditing can get pretty technical and should be approached with careful planning and consideration. Despite this though, don’t let potential complexities deter you from using this great feature on SQL Server. It sure beats triggers and traces…

And finally if you already have this in place, it’s also worth reviewing and updating this periodically to see if the coverage is complete, appropriate and up to date. Don’t assume it’s there or it’s appropriate…

 


Leave a Reply