The hardest part of Security Auditing is to define what should be audited and how. The security breaches around a SQL Server instances may occur in several areas: from SSIS packages saved on disk, to system users and administrators who can read and even modify data. There a lot of different attack vectors to monitor. A good place to start is to determine the legislative requirements for your installation, which will depend on the nature of the data. Generally, there is a requirement to track and log events that occur on the Database Engine in sufficient detail to aid a forensic analysis of any data-breach that occurs.

The next challenge, after defining what should be audited, is to find a way to take a snapshot of the system’s security context at the time of the start of the auditing. The idea is that we must know what the settings are at the beginning of the auditing process, so we can monitor deviations and send alerts based on them.

Finally, another challenge is to choose a way to deal with the large volume of data generated by the security auditing. The data can be stored in logs (SQL Server instance logs, Operating system logs), it can also be stored in log files on disk. Ideally, the data can be stored in ring buffers and consumed by external components and so on, but all this needs to be planned around the audit retention interval that is required.

The work of setting up an audit log has been made much easier by Microsoft’s introduction of SQL Audit functionality. It has been available in SQL Server since SQL2008 and it was slightly improved in SQL2012. In this article first show how to set up the current security context, and then we’ll show you how to set up SQL Audit and apply it to all the databases in an instance.

How to take a snapshot of the current security context

First, we will create a database called SQLAudit and a table in it called AuditDBLogin.

Then we will run the following script to get the database logins and all permissions for the databases.  It will need to run every database you need to audit on the instance that we are interested in auditing:

this routine will provide a security snapshot that can be used to verify and note who has what permissions. Then, the SQL audit itself keeps track of changes to the permissions; and reporting can be done if anything significant is altered. This routine is needed only as a reference to understand any changes that are subsequently shown by SQL Audit to the permission system.

Audit everything – scripts for 2008 and 2012

Now we are ready to create the SQL Audit. First, we will start by creating an Audit for the Server itself: this is an object on a SQL Server instance level which is used to specify the way that the data is channelled and stored. The Server Audit may contain a Server audit specification (events on an instance level) and database audit specifications (events on a database level). The data can be stored either in binary file on disk, or written to the Windows Server application or security logs.

Keep in mind that only Enterprise / Developer editions support database level audit specifications.

Creating the Server Audit:

In this case I have chosen to save the data into a binary file on the disk system. There will be two files of 200Mb each, which will be rotated and overwritten as they reach their 200Mb size limit.

Creating a Server Audit Specification

As I’ve already mentioned, the Server Audit Specification collects events on a SQL Server instance level. The script will check for the version of the SQL Server and will execute different sections based on whether it is SQL 2008 or above, since there are extra audit events in the SQL 2012 and up.

Creating the database audit specification

Sadly, the database audit specifications are only available in Enterprise / Developer editions.

The script below iterates through all databases on the instance and creates the database audit specifications, including the model database, which will automatically bring the audit into any newly created databases.

How to read the log

Now that we have setup the Server and Database level auditing, lets see what is in our audit file.

We can read the binary file from SQL Server query window via the following script:

The outcome of the query will look similar to this:

As we can see, there is a column called action_id, which points to a specific action that was performed.

The actions can be listed by using the following query:

The output will look like this:

There are over 500 actions that are audited, and we can see how many actions are triggered in our Audit by running the following query:

The result will look similar to this:

Automate and centralize

The technique and scripts above will audit everything – all the possible events on server and database level. Depending on the requirements, there can be different approaches on how to go from here. You have to be able to analyze and query the log.

There is a built in function in SQL Server which will query the log: sys.fn_get_audit_file. There are a few issues to be aware of, however. In a busy production system there might be Gigabytes of log data created in a few minutes, which is a challenge in itself to process.

Another caveat is that if the log is written to files, as suggested earlier in the article, a special attention needs to be paid to the file locking mechanisms during the log file reading and the rollover / deletion process. In other words, if there is a process which is using the sys.fn_get_audit_file function to read the audit files, then it will create a lock on the files and they wont be able to be deleted after rolling over, until the reading process is stopped.

A workaround would be to use the Security log in Windows to write the audit events instead of the files on the file system. This way, the locking can be avoided and the rollover and log recycling is a bit easier. However, this creates another issue – some tweaking needs to be performed so the SQL Server account can have the access to write to the Security log, since it does not have the permission by default.

This is done by giving access to the HKLM\SYSTEM\CurrentControlSet\Services\Eventlog\Security key to the SQL Server account and also add the SQL Server account in the Generate Security Audits local group on the server. Of course, a SQL Server instance restart is needed.

There are quite a few options when it comes to processing the collected data. I cannot say that SQL Server has anything ready “out of the box” which will help the user do a full audit investigation and reporting. There are several ways to go from here and the most popular ones include third party tools like ELK, Splunk or EXASOL to get the data and index it in order to do reporting and alerting on it.

It really depends on what the goal of the audit is, how much data is generated and how much of it needs to be analyzed, and the answers to these questions will dictate the choice of tools down the road.

ELK is a great open source tool, the name stands for Elastic Search + Logstash + Kibana.

• The pros here are that it is a free, open source stack, which can handle the centralization, the indexing and the graphical display of realtime events from the Security Audit events.
• The cons here are that there is no support, a flexible Linux-based architecture needs to be setup in-house, and if there is no competence in-house, it will cost some time and funds.

Splunk is another option to go for

• The pros here are that it is fast, flexible, easily handles large volumes of data and supplies realtime reporting and search functionality. Splunk has a great support and staff which will setup everything for you.
• The cons here are that the solution costs a lot, and the bill comes by GB of data per day which is indexed and stored in Splunk. So, if you have a busy system, generating 50GB of data per day in security logs (I have personally seen much more than that!) you can imagine that it comes at a considerable cost.

EXASOL is a very fast in-memory database which has a great capability to store and process large volumes of data.

• The pros here are that it is fast, flexible, easily handles large volumes of data, it is fairly easy to setup and maintain
• The cons here are that the solution has costs associated to it, and it does not have its own reporting front-end. It is teamed up with Tableau, and reporting if fairly easy and fast, at a certain cost, of course.

In short, there is no real ‘out of the box’ solution provided by Microsoft to help us process the security audit data, thus we have to find a way to bring in other third party tool, depending on the audit goal and the estimated capacity requirements.

Audit the audit

A few words on the security of the security auditing: it is understood that sysadmins and db_owners can tamper with the Server Audit specifications and the Database audit specifications. One way to deal with this is to create another audit which audits the events related to the AUDIT_ CHANGE_GROUP.

This can be done with the following script:

The AUDIT_CHANGE_GROUP event is raised to alert on creation, modification or deletion of audit events. So in this case there will be two audits – one that audits the security events in the system and the other will be auditing if admins are tampering with the first audit.