Creating and managing Audits in SQL Server

In this blog I will be talking about Creating and Managing Audits in SQL Server. We will first look at what SQL Server audit is and why you should use it. After this we will be looking at where to configure and how to activate SQL server audit, server audit specifications and database level specifications. To finish, we will see how to access the collected data from the sql audit file.

What is auditing?

“Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine“, is the official definition as found on the Microsoft docs ( https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017 )
This makes Auditing an important feature in SQLServer (since SQLServer 2008) regarding security. It helps you log and understand database or server activity. SQL Server audit specifications can be set up at the server and the database level to track the activities in which you may be interested. Prior to SQL 2016 only the Enterprise, Developer and Evaluation supported this feature. Since 2016 standard edition also supports this feature (drinks).
Why should I use it? Data security these days is becoming more important each day. When using SQL Server audit you have a way to get an insight into discrepancies and anomalies that might indicate business concerns or suspected security violations like certain users trying to access data which they aren’t supposed to. Since it is a build-in feature you won’t need to spend extra company resources on a 3rth party application.

Configuring and activating SQL audit.

To configure and activate a SQL audit we must first know that there are 3 different objects that we can configure before activating SQL audit.  We have the Audit itself, this can be found on server level in the security folder as seen on figure 1. We have the server level Audit specifications also seen on figure 1. And as 3th item we have the Database Level Audit specification, these can be found in the security folder on database level as seen in figure 2.
First we will be talking about Audits since that would be the first step in setting up SQL Server audits.

Figure 1                                                                                                       Figure 2                                                   

1. Audits: When creating this object you will need to specify the following properties (see figure 3):
   a) A name. Could be anything but I recommend using a strict naming convention so you always know with a glance what that audit is supposed to do.b) Queue delay. If the value equals 0 this means that audit actions will be processed synchronically. If another value has been inputted the action will be processed after x amount of time, this has potentially less of an impact on performance but there is a risk that some events get lost.c) Audit log failure. There you can select how SQL Server behaves when the database engine fails to write an event to the audit file.
   • • Continue. Activating this option the operation will continue without any hindrance. The audit will continue to try to log events. This shouldn’t be used when it is required to always log certain events
     • Shut down server. This forces the database engine to shut down if it is not possible to write to the audit log.
     • Fail operation. This option makes the database actions that needed to be logged fail if it’s not possible to log said action. Other actions aren’t impacted. Once the failure condition is resolved the audit will resume to write these actions down.

   d) Audit destination. Here you define as what the audit will be stored. This can be as a separate file, windows security or windows application log.

   e)  File path. Here you define where the file will be stored. I created a separate audit map where I store my file.

   f) Audit file maximum limit. With these options you configure how big the files can become, how many files there should be and if they will rollover.

2. Server Audit Specification. Here you control which server level events that need to be documented on the SQL audit log which you specify in the audit option as seen in figure 4 where I would log every event specified to the TestAudit. You can only have 1 of these on each Audit.

3. Database Audit Specification. Here like in point b you need to specify which audit to use and which events to log. Again only 1 on each audit so if you would like to audit several databases you will need to make several audits.

Getting information out of the audit log

The audit log (if using a binary file) can be done by going in the object explorer and right clicking the audit and selecting “view audit logs” this will give you figure 4 as new window. My log doesn’t really have much info at the moment but if you have configured some specific events these will be visible in this window. The box at the bottom will give you a very detailed description about the event that occurred.

Figure 4

Now we have seen how you can create and manage your SQL Server audit and how you can access the data that has been logged. Now you can go experiment with all the different events that can be captured in the Audit but watch out because one of the dangers would be that if you just find out of this feature you may be tempted to simply audit everything, this way you could check anything, right?

Of course you can do it like this but this will give a big performance impact, it is better to configure several distinct audits and swapping between these whenever you need a specific result this because the more events being audited, the less resources SQL Server will have available for other tasks.

Which events are best to log is or are more valuable is a topic for another day.

Thank you.