Auditing SQL Server

Jonathan Gardner SQL Leave a Comment

“An auditor is someone who arrives after the battle and bayonets all the wounded”

It is the time of year in the United States that the word “audit” conjures up images of the Internal Revenue Service dragging us across a bed of hot coals. But that is not the case when we talk about it in reference to SQL Server. In SQL Server 2005 and earlier auditing was done through a combination of tools such as triggers, Windows logs, and SQL Trace. With the release of SQL Server 2008, Microsoft introduced auditing as a feature in the database engine to satisfy requirements around regulatory compliance including HIPAA, Sarbanes-Oxley, and PCI.

Auditors

Flash forward to SQL Server 2012, auditing is a valuable addition to a DBA’s toolset. It gives us a very granular way to audit activity on a server, all the way down to specific database objects.

Auditing is made up of three components: Server Audit, Server Audit Specification, and Database Audit Specification. We will break these down and set up auditing on our “new” old friend, AdventureWorks2012.

Server Audit

The SQL Server Audit tells us where we store the audit data. This location can be either the Windows Application log, Windows Security log, or a log file. A Server Audit is specific to a single instance of SQL Server but you can have more than one Server Audit per instance. This allows administrators the granularity to store different audit information in different locations if required.

--Create a server audit called Corp_Audit in the folder C:AuditLogs.CREATE SERVER AUDIT Corp_AuditTO FILE (FILEPATH = 'C:AuditLogs'

The audit option ON_FAILURE dictates what the server should do if the file cannot be written to. While shutting down the server if the audit file cannot be reached may sound extreme, it is required by some standards or companies.

By default the Server Audit is disabled.

--Enable the Server AuditALTER SERVER AUDIT Corp_AuditWITH (STATE = ON)GO

At this point, the Server Audit is turned on but it is not auditing anything. With the location of the audit set we can now define Server Level events to audit and database level events. We will start with Server Level events.

Server Audit Specification

Server level events like Backup, Restores, Failed Logins, etc can be audited through the Server Audit Specification. The Server Audit Specification can be created and turned on in the same command.

--Create and enable the Server Audit SpecificationCREATE SERVER AUDIT SPECIFICATION Corp_Server_Audit_SpecFOR SERVER AUDIT Corp_Audit  ADD (BACKUP_RESTORE_GROUP),  ADD (DATABASE_OBJECT_CHANGE_GROUP),  ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),  ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),  ADD (FAILED_LOGIN_GROUP)WITH (STATE = ON)GO

Database Audit Specifications

Database level events like SELECT, UPDATE, INSERT, DELETE are tracked through the Database Audit Specification. The Database Audit Specification can also be created and enabled in the same command. The following code will create a database audit on the EmployeePayHistory table so we can find out when someone with dbo privileges queries or modifies the table.

--Create and enable the Database Audit SpecificationUSE AdventureWorks2012GO  CREATE DATABASE AUDIT SPECIFICATION Corp_AdventureWorks2012_Database_Audit_SpecFOR SERVER AUDIT Corp_Audit    ADD (SELECT, UPDATE, INSERT, DELETE ON HumanResources.EmployeePayHistory BY dbo)    WITH (STATE = ON)

Now that we have the audit file set up we can perform tasks on the database. As we perform tasks the information is stored in the audit file.

Query the Audit File

We have set the audit to store information in a file instead of the Windows logs. To query the file the fn_get_audit_file function is used to query it.

--Query the audit fileUSE masterGOSELECT * FROM   sys.fn_get_audit_file('C:AuditLogsCorp_Audit_*.sqlaudit',DEFAULT,DEFAULT )GO

We have coved how to set up auditing and have used some examples of audit actions. A full list of the options on both server and database level audit actions and groups can be found here. Do you have a specific auditing question? We would love to hear from you in the comments below.

References

Jonathan GardnerAuditing SQL Server