Performing Auditing in SQL Server environments

In this post I would like to talk about SQL Server Audit. This new way of auditing was introduced with SQL Server 2008.This feature is supported in SQL Server 2008 Enterprise edition.Auditing is simply the process of monitoring and tracking changes to the SQL Server system.All the DBAs need to implement auditing at some point to satisfy business requirements. There is something called  “SQL Server Extended Events” engine.The extended events engine allows any process to define and raise events and consumers to receive events.This engine is in the heart of SQL Server Audits.

Some of the advantages of SQL Server Audit over the other auditing techniques available to us(I am going to mention some of them later on) are the fact that you can perform audits at the server and database levels.We can define audits for different activities at the same time and capture activity generated from a variety of SQL Statements like select,insert,update,delete,execute.As with so many activities in SQL Server you can set up and administer SQL Server Audit using SSMS.

There are some limitations as well when using SQL Server Audit.As with anything in SQL Server everything takes up resources.If you decide to have a very detailed auditing then more resources are used. So one thing to keep in mind is not to use SQL Server Audit in servers where there are heavy workloads and are experiencing bottlenecks. Or if you are adamant about using them then use them carefully.One other thing to keep in mind is that SQL Server Audit is instance based.So you will have to set it up on every instance. The data is stored in the event logs or some file. If you need to have further analysis on them you should import them on some tables in a database.

SQL Server Audits are comprised of Audits,Server and Database Audit specifications,actions and action groups and targets.

There are some other alternatives that we can use in order to perform auditing to our SQL Server instance. I am going to talk about them briefly.

We can use C2 Audit mode.It configures SQL Server to record attempts to access statements and objects.

C2 audit mode can still be configured in SQL Server but is now rarely used. It has a negative performance impact through the generation of large volumes of event information.

When you set this option you should be prepared to run out of disk space.If this happens SQL Server will shut itself down.That is why I think it is imperative for everyone to check through jobs and alerts the remaining disk space where our databases live.You can turn on C2 Audit mode through SSMS. Just select the instance of SQL Server from the Object Explorer right-click and select Properties. Then choose Security.

Have a look at the picture below

We can turn C2 audit through T-SQL as well.

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'c2 audit mode', 1 ;
GO
RECONFIGURE ;
GO

We could also use triggers for auditing.I have blogged about triggers in this post. We can have DML triggers for data modification and Logon triggers for tracking logons.There are some limitations using triggers like performance issues, the ability to disable triggers e.t.c

One of my favourite tools in the SQL Server ecosystem is SQL Server Profiler.We can use SQL Server profiler to trace commands sent to the server.

The basic process in order to create an SQL Server Audit is the following:

We need to define a SQL Server Audit Package and define a destination for the output.

Then we need to define a Server Audit Specification and a Database Audit Specification.

Then we need to enable the Audit. Finally we need to analyse the captured audit events.We can view the audit using the Windows Event Viewer, the Log File Viewer or by using the fn_get_audit_file function.

A server audit specification determines which server level events shoud be included in the audit.Server audit specification are defined at the SQL Server instance level.We will use it to see who is logging in or out from our server,logins that are dropped and added to fixed server roles and backups/restores that are performed.

A database audit specification works like the server audit specification but at the database level.We will use it when we want to know who is selecting,inserting,updating data in a table in a database.

Have a look at this link for a complete list of Server-Level Audit Action Groups and  Database-Level Audit Action Groups

Let’s see all that with a hands on example.

In case you do not have an instance of Enterprise edition or Developer edition  of SQL Server 2008 / R2, you can download it from here . This is a trial version.

We need a database as well. I will use the Northwind database.

If you need the installation scripts for the sample Northwind database, click here

1) Launch SQL Server Management Studio and connect to the local instance of SQL Server 2008/R2 Enterprise or Developer edition.

2) We need  to create a new audit object. Through SSMS, we open  “Security,” and you will see the “Audits” folder and then choose the “New Audit ” option, as shown in the picture below.Now we need so specify the details of the Audit.

The first thing I need to do is to assign an audit name. Then I specify a  “Queue Delay” number. This is the delay expressed as an amount of time after an audit
event has occurred and before it is written to the log. The default value is 1000 milliseconds.

Then I have the option of  “Shut down server on audit log failure”. By selecting this option and for whatever reason the audit data can’t be logged, SQL Server will not start, unless you manually start it.Then we have to choose the option where to store the audit data. I am going to choose “Application Log” and then click “OK”.

Then I will create the server audit specification.We have to choose from the Object Explorer , Security –> Server Audit Specifications –> New Server Audit Specification.Then I need to choose a name for the Server Audit and “link” it to the audit object previously created, namely “MyServerAudit”.Then I choose the Audit Actions.In this example I am interested in

• FAILED_LOGIN_GROUP
• SERVER_ROLE_MEMBER_CHANGE_GROUP
• BACKUP_RESTORE_GROUP

I will not go into details because these are self-explanatory.After I do that I just click “OK” to close the window. Then I select the server audit specification I created “MyServerAuditSpecification”, I right-click on it and then enable it.

Have a look at the picture below

Now we can create an audit specification for the Northwind database.We do that by using again SSMS and the Object Explorer to open up the database to be audited. Then we open up the security folder under it. Next, right-click on “Database Audit Specifications” and select “New
Database Audit Specification”.The “Create Database Audit Specification” dialog box appears.We can define a name for the audit. I named it “MyDatabaseAuditSpecification”.

Then we select the appropriate audit object from the Audit dropdown box. In my case is “MyServerAudit”.Then we need to define the kind of audit activity we want to capture by selecting from the “AuditAction Type” drop-down box.In the Actions list I will choose to audit (INSERT,SELECT,UPDATE) of the Categories table of the Northwind table.I will choose these actions from the Audit Action Type dropdown and then choose the Object Class (OBJECT) and then the Object Name (Categories) and the Principal Name (dbo). Then I click “OK” to close the window.Then I need to enable my audit specification.

Have a look at the picture below to see all the settings we need to set.

Now let’s test the audits. We will have to write some T-SQL statements.We will write some T-SQL statements to test the Server Audit specification.

The events I have specified in the Audit are

FAILED_LOGIN_GROUP – Indicates that a principal tried to log on to SQL Server and failed

SERVER_ROLE_MEMBER_CHANGE_GROUP – This event is raised whenever a login is added or removed from a fixed server role.

BACKUP_RESTORE_GROUP – This event is raised whenever a backup or restore command is issued

--I create the login and then try to log on to the server as the new user but intentionally typing the wrong password

CREATE LOGIN myuser WITH PASSWORD ='password'

--see what logins are members of the sysadmin role

sp_helpsrvrolemember 'sysadmin'

--add myuser login to the sysadmin fixed server role

sp_addsrvrolemember 'myuser','sysadmin'

-- drop the user to the sysadmin fixed server role

sp_dropsrvrolemember 'myuser','sysadmin'

--perform a full backup of the Northwind database
BACKUP DATABASE [Northwind] TO
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Northwind.bak' WITH NOFORMAT,
NOINIT,  NAME = N'Northwind-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Let’s open the “Application Log FileViewer” by selecting “Computer” icon from the desktop and right-click choose “Manage” and then choose “Event Viewer” –> “Window Logs”  –> Application.

Have a look the picture below that shows that the full backup operation audited.

Have a look the picture below that shows that the failed login operations audited.

Have a look the picture below that shows that adding/dropping a login to a fixed server role operations were audited.

Now we will move on to test the database audits.We need to write Insert,Update,Select statements for the Categories table of the Northwind database.

USE Northwind
go

SELECT * FROM dbo.Categories

UPDATE dbo.Categories
SET CategoryName = 'Drinks'
WHERE CategoryID=1

INSERT INTO dbo.Categories

VALUES (9,'General food','Bread,cheese,meat')

In this picture below the INSERT statement is audited.

In this picture below the SELECT statement is audited.

In this picture below the UPDATE statement is audited.

That is all folks!!!I hope you appreciated the power of SQL Server Audits.

Hope it helps!!!