Audit on SQL Server

ShowImageThe other day one of my clients asked me if there was any way to audit what users did on their data, and in particular some tables of importance. I had to look it up, well I knew that there was some Audit possibilities build into SQL Server, but I had never had the need for it before. And as I was expecting, SQL server offers a rather thorough audit engine, you just have to enable and configure it.

I’ll show you what is needed to set up audit on tables and views in a given database, but first a little bit about the audit engine it self.

What is Audit on a SQL server

Audit is an engine that gives you the ability  to log the use and access to data in tables, views, execution of stored procedures, functions and much more.

Here is a short list of some of the possibilities for auditing on database level:

      • SELECT
      • UPDATE
      • INSERT
      • DELETE
      • EXECUTE

Here is a short list of some of the events to audit on server level (not adequate list) :

      • DATABASE_PRINCIPAL_IMPERSONATION_GROUP
      • DATABASE_ROLE_MEMBER_CHANGE_GROUP
      • DBCC_GROUP
      • FAILED_DATABASE_AUTHENTICATION_GROUP
      • FAILED_LOGIN_GROUP
      • FULLTEXT_GROUP
      • LOGIN_CHANGE_PASSWORD_GROUP
      • LOGOUT_GROUP

As you can see there is an vast variation of events to audit, and depending on the level of regulation in a clients business, we can set up different levels of auditing on various objects.

It gives you the option to store the log in binary files, the security log or the application log.

To enable AUDIT in SQL server, there is a few things that needs enabling first.

First there needs to be created a new AUDIT on server level, this can be accomplished either in SSMS or by T-SQL script, If you go for SSMS it is done under Security.

1. Right click on Audits and then click on New Audit

image2. You should now see the window below.
image3. It is now time to define how  the audit should store the log, the name of the audit, file sizes and the like.
4. Either store the log in a file or in the Security or Application Log
5. If possible you should reserve the disk space now, to avoid a failure if the disk runs out of space.

If you decide to do is by T-SQL here is a script that do exactly that.

USE [master]
GO

/****** Object:  Audit [Select Audit AdventureWorks2014] ******/
/****** Create the Server audit for use in the database audit ******/
CREATE SERVER AUDIT [Select Audit AdventureWorks2014]
TO FILE 
(	FILEPATH = N'D:\MSSQL\2014\Audits\'
	,MAXSIZE = 500 MB
	,MAX_ROLLOVER_FILES = 20
	,RESERVE_DISK_SPACE = ON
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = 'd1817028-0edc-49d3-bbad-0312291673c9'
)
ALTER SERVER AUDIT [Select Audit AdventureWorks2014] WITH (STATE = ON)
GO

The above script will make a file log on D:\MSSQL\2014\Audits\ with a max file size of 500mb, 20 files and the space will be reserved for the log.
Next we issue an alter statement on the server audit just created, where we enable it.

Now that the server audit is created and active, it is time for the actual database audit specification, again we can create it in SSMS or by T-SQL – first SSMS

image1. Right click on Audits and then click on New Audit
2. Click on New Database Audit Specification, and the following window will show

image3.Here you can choose what audit action types to audit, here its DELETE, then select the object, object schema and click OK.
4. That is it, your now auditing for DELETE in the specified object.

If you would use T-SQL here is a script that does the same, just for a few more objects.

USE [AdventureWorks2014]

GO

/****** Create the Audit SPecification for these objects ******/
CREATE DATABASE AUDIT SPECIFICATION [DB Select Audit AdventureWorks2014]
FOR SERVER AUDIT [Select Audit AdventureWorks2014]
ADD (SELECT ON OBJECT::[HumanResources].[Department] BY [HumanResources]),
ADD (SELECT ON OBJECT::[HumanResources].[Employee] BY [HumanResources]),
ADD (SELECT ON OBJECT::[Person].[Person] BY [dbo])
WITH (STATE = ON)
GO

As you can see we have defined audit for select on 3 different objects, Department, Employee and Person. This means that every time an application or user makes a SELECT on either of these objects an audit entry is made.

If we then views the log, this is what we will see.

image

As you can see, i have issued an “select * from humanresources.department” and you can easily see from which workstation and use the data was viewed by.

So it is fairly easy to setup, and even easier to use – so my message to you is this. Always set up some sort of audit on at least sensitive data, not all, and not each and every audit action, but the ones that allows you to spot any malicious activity on your data.

Happy Auditing ;o)

Leave a Reply

One comment