How to Configure SQL Server Auditing

With the GDPR requirements rattling quite a few trees in the industry, there’s been a renewed interest in SQL Server’s auditing capabilities that were once a very neglected feature. This article steps you through setting up both a server level audit, as well as a table auditing on specific events such as selects, updates and delete operations which can help your business’ towards becoming more compliant with these new data protection standards.

Now if you’re planning on logging to the security or application logs, you may want to increase the max size of your log files to make sure you’re not just capturing a rolling 1 hour of auditing data.

ENABLE SERVER AUDITING

The below code provides two options for auditing to the application log, or do a data file. This can be updated via the GUI also after-the-fact, but will require the auditing to be momentarily stopped and restarted again while making that change.

USE [master]
GO

CREATE SERVER AUDIT [SQL_Server_Audit]
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO

-- OR --

CREATE SERVER AUDIT [SQL_Server_Audit] 
TO FILE ( FILEPATH = 'B:\SQLAudit' )
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO


Alter Server Audit [SQL_Server_Audit] with(State=ON)
GO

Next up we’ll need to define a server audit specification. These are the server-level auditing components that we’re going to want to capture as part of this audit. A sample specification is as follows, noting that you’ll want to change this to suit your business requirements;

CREATE SERVER AUDIT SPECIFICATION [Server_Audit_Specification]
FOR SERVER AUDIT [SQL_Server_Audit]
     ADD ( AUDIT_CHANGE_GROUP )
     ,ADD ( BACKUP_RESTORE_GROUP )
     ,ADD ( DATABASE_CHANGE_GROUP )
     ,ADD ( DATABASE_OWNERSHIP_CHANGE_GROUP )
     ,ADD ( BROKER_LOGIN_GROUP)
     ,ADD ( DBCC_GROUP )
     ,ADD ( LOGIN_CHANGE_PASSWORD_GROUP )
     ,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP )
     ,ADD ( SERVER_PRINCIPAL_CHANGE_GROUP )
     ,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )
     ,ADD ( DATABASE_PRINCIPAL_IMPERSONATION_GROUP )
     ,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )
     ,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
     ,ADD ( FAILED_LOGIN_GROUP )
     ,ADD ( SUCCESSFUL_LOGIN_GROUP )
WITH ( STATE = ON);
Go

At time of this blog post, the following are all the available options for server-level auditing;

SERVER LEVEL AUDITING

ENABLE DATABASE AUDITING

Database auditing requires that a server audit (although not necessarily server audit specification) to be in place. The DB auditing however is created within the user database that is to be audited, rather than within the master database where the server audit gets created. Database audit specifications can be found within the DB itself under Security –> Database Audit Specifications.

To create a database audit, you’ll need to first USE the DB (to select it), then the following provides an example syntax for auditing SELECT, UPDATE and DELETE operations for specific tables within that database;

USE UserDatabase
GO

CREATE DATABASE AUDIT SPECIFICATION [User_Database_Audit_Specification]  
FOR SERVER AUDIT [SQL_Server_Audit]
       ADD (SELECT , UPDATE , DELETE ON UserDatabase.dbo.Customer_DeliveryAddress BY dbo )
       ,ADD (SELECT , UPDATE , DELETE ON UserDatabase.dbo.DimCustomer_Email BY dbo )
       ,ADD (SELECT , UPDATE , DELETE ON UserDatabase.dbo.DimCustomer_Phone BY dbo )
WITH (STATE = ON) ;   
GO

The SELECT, UPDATE and DELETE operations aren’t the only things you can add to the audit specification though…

For a full list of database level auditing options (at time of this blog post), see below;

DATABASE LEVEL AUDITING