Security Audit on SQL Server 2008

This week while I was working on Bolivia, I overheard the security staff discussing about options they can use to audit transactions done on the database tables. I interrupted and asked them if they knew about the SQL Audit configuration on SQL 2008, and they told me they didn’t. So wrote this post.

The first question is: Why do we audit?

Reviewing some audit security books, the authors agree on that having security policies is a critical factor to maintain data secure. Audit will help us to identify what an intruder access in case he succeeded on a security breach.   

On SQL 2005 we were oriented to be sure the users only had the specific privilege that they needed to do their jobs and changes can be only made be authorized staff. The tool that we proposed to have some kind of audit was a SQL trace, because it can track changes on the schema, insert update and delete operations and events related to permissions or the creation of new logins.

One of the enhancements on SQL Server 2008 Enterprise is the capacity to audit actions through SQL Server Audit. With this functionality you can track and log automatically the events on the SQL Server or per SQL Database, this is possible using and Audit Object. Will see how to create an Audit, how to create and enable an Audit Specification at the Server and Database level and how to visualize the Audit records.

Create an Audit

An audit object is a collection of one or more activities, or a group of activities that can be track. For example, you can configure and audit object to identify all the failed logins. The events are written on the specified location, it can be store on a file, on the Windows Application Log or the Windows Security Log.

The Audit Object can be created through SQL Server Management Studio (SSMS) or T-SQL. Using SSMS you should click over the New Audit option located on the Audit folder under the Security Tree, as shown here:

On the Create Audit screen you need to introduce the name for the Audit Object, and specify the location. If you choose a file as the location, you will need to specify the file path. Finally click OJ to create the object.

For propose of the example I have created a second audit object with the name “AuditarConsultasTablaEmpleado”. The location is a file on a specific path. Both objects are located on the Audits Folder, as shown on the figure. This audit object is going to be used to track the SELECT transactions executed against the HumanResources.Employee table on the AdventureWorks2008R2 database.

If you want to create an audit object trough T-SQL you can do it using the CREATE SERVER AUDIT comand. The following query creates the object “AuditarConsultasTablaEmpleado”. This was already done using SSMS.

USE master

CREATE SERVER AUDIT [AuditarConsultasTablaEmpleado]

TO FILE (FILEPATH = N'C:\TEMP');

Create and enable an Audit Specification at the Server level

Once that you create the audit object, the following step, is to create the appropriate specifications for the audit. The audit specifications tell the audit object what it needs to track. For the audit object “AuditarLoginsFallidos”, we need to create a specification the looks for failed logins. To accomplish this we can right click on the Server Audit Specifications folder on the security tree.

Provide the audit specification name, like “EspecificacionAuditoriaServidor-LoginsFallidos”. On the Audit option select “AuditarLoginsFallidos”. This will assign the audit specification “EspecificacionAuditoriaServidor-LoginsFallidos” to the audit object “AuditarLoginsFallidos”. Select the type of action to audit: “FAILED_LOGIN_GROUP” and press OK to create and assign the object.

Right click on the “EspecificacionAuditoriaServidor-LoginsFallidos” to enable the specification through the option: “Enable Server Audit Specification”.

Finally you need to enable the audit object by right clicking on the “AuditarLoginsFallidos” to enable the audit object through the Enable Audit option as shown on the figure.

If you want to create an audit specification at the server level trough T-SQL, you can do it using the command CREATE SERVER AUDIT SPECIFICATION. The following statement creates the specification audit “EspecificacionAuditoriaServidor-LoginsFallidos”. This was already done through SSMS.

USE master

CREATE SERVER AUDIT SPECIFICATION [EspecificacionAuditoriaServidor-LoginsFallidos]

FOR SERVER AUDIT [AuditarLoginsFallidos]

ADD (FAILED_LOGIN_GROUP) WITH (STATE = ON)

GO

Create and Enable an Audit specification at the Database level:

To create and enable an audit specification at the Database level you should expand the database, on this example we are using AdventureWorks2008R2, and right click on the “Audit Specifications” under de security tree of the database and select the option “New Database Audit Specification”, them assign a name (for this example we are using the name “EspecificacionAuditoriaDB-ConsultasTablaEmpleado”)

As shown on the figure, select the option “AuditarConsultasTablaEmpleado” on Audit. This will assign the audit specification “EspecificacionAuditoriaDB-ConsultasTablaEmpleado” to the audit object “AuditarConsultasTablaEmpleado”.

Note: The object Class indicate what do you want to audit, the options are: Object, Database or Schema. Select Object to audit Tables, Functions, Store Procedures or Views. The principal name is the entity that can access the SQL Server resources.

For the purpose of the example, use “SELECT” on the type of action to audit; on the object class use “OBJECT”; on the object schema and name use [HumanResources].[Employee]; on the Principal name use [public] an press OK to create and assign the audit object.

This will allow to track the select queries done by all users to the [HumanResources].[Employee] table.

Right click on “EspecificacionAuditoriaDB-ConsultasTablaEmpleado” to enable the audit specification using the option: “Enable Database Audit Specification”

Finally to enable the audit right click on “AuditarConsultasTablaEmpleado” to enable the audit trough the option Enable Audit as shown on the figure.

If you wish to create the audit specification at the database level using T-SQL, you can do it using the command CREATE DATABASE AUDIT SPECIFICATION. The following query creates the specification audit “EspecificacionAuditoriaDB-ConsultasTablaEmpleado”. This was already done through SSMS.

USE master

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec-EmployeesTable]

FOR SERVER AUDIT [Audit-EmployeeQueries]

ADD (SELECT ON OBJECT::[HumanResources].[Employee] BY [public])

WITH (STATE = ON)

GO

Visualizing the Audit Logs

The audit logs can be Access through the “View Audit Logs” option or through the Application or Security event log, depending where you specified to store the audit.

Results can be filter or exported on the following formats: log, csv and txt

Conclusion

The SQL Server 2008 Enterprise Audit options are pretty powerful and flexible; this will allow you to create audits at the server or database level. The configuration is pretty simple because you only need to specify where the audit is going to be stored (on a file, Windows Application Log or Security Log), what object you want to audit (Server, Database, Table, Schema, Function, Store Procedures and Views) and for which Principal (entities that can access the SQL Server resources).