Create audit objects in Microsoft SQL Server for the Splunk Add-on for Microsoft SQL Server

The Splunk Add-on for Microsoft SQL Server includes support for monitoring audit data from Microsoft SQL Server. For more information, search for "SQL Server Audit (Database Engine)" on the MSDN web site. By default, auditing is disabled in SQL Server, so you must create the audit objects in your SQL Server instance in order for the Splunk platform to ingest this data. If you skip this step, the add-on does not collect audit log data, but the other inputs still function.

You can configure auditing in Microsoft SQL Server at the server level or at the database level. Manage the audit level by configuring audit action items that target server-level operations, database-level operations, or individual operations on a database table, view, or stored procedure. See "SQL Server Audit Action Groups and Actions" in the Microsoft SQL Server documentation for a full guide covering how to set up audit action groups and actions.

The more types of audit specifications you monitor, the larger the audit events indexed by the Splunk platform.

Create audit objects and specifications using SQL Server Management Studio or Transact-SQL. See "Create a Server Audit and Server Audit Specification" in the Microsoft SQL Server documentation for a step-by-step guide.

The following examples demonstrate how to create and enable audit objects at different possible levels.

Example of creating an audit object at the server level

Create the C:\SQLAudit directory first.

1. Create a server-level audit object MSSQL_Server_Audit with a file path C:\SQLAudit.

   USE master ;
   
   -- Create the server audit.
   
   CREATE SERVER AUDIT MSSQL_Server_Audit TO FILE ( FILEPATH = 'C:\\SQLAudit' ) ;
   
   -- Enable the server audit.
   
   ALTER SERVER AUDIT MSSQL_Server_Audit WITH (STATE = ON) ;
   

2. Create an Audit Specification MSSQL_Server_Specification in the master database and attach it to the audit object. This Audit Specification audits the following groups. APPLICATION_ROLE_CHANGE_PASSWORD_GROUP, BROKER_LOGIN_GROUP, DATABASE_CHANGE_GROUP, DATABASE_LOGOUT_GROUP.

   USE master;
   
   CREATE SERVER AUDIT SPECIFICATION MSSQL_Server_Specification 
   FOR SERVER AUDIT MSSQL_Server_Audit 
   ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), ADD (BROKER_LOGIN_GROUP), 
   ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_LOGOUT_GROUP) WITH (STATE = ON) ;
   

Example of creating an audit object at the database level

1. Create a database-level audit object MSSQL_Database_Audit with a file path C:\SQLAudit.

   USE master ;
   
   CREATE SERVER AUDIT MSSQL_Database_Audit TO FILE ( FILEPATH = 'C:\\SQLAudit' ) ;
   
   -- Enable the server audit.
   
   ALTER SERVER AUDIT MSSQL_Database_Audit WITH (STATE = ON) ;
   

2. Create the Audit Specification MSSQL_Database_Specification in the master database and attach it to the audit object. This Audit Specification audits the following groups. APPLICATION_ROLE_CHANGE_PASSWORD_GROUP, AUDIT_CHANGE_GROUP and DATABASE_LOGOUT_GROUP.

   USE master;
   
   CREATE DATABASE AUDIT SPECIFICATION MSSQL_Database_Specification 
   FOR SERVER AUDIT MSSQL_Database_Audit 
   ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), 
   ADD (AUDIT_CHANGE_GROUP), ADD (DATABASE_LOGOUT_GROUP) WITH (STATE = ON) ;
   

Example of creating an audit object for a table

1. Create a database-level audit object MSSQL_Table_Audit with a file path C:\SQLAudit.

   USE master ;
   
   -- Create the server audit.
   
   CREATE SERVER AUDIT MSSQL_Table_Audit TO FILE ( FILEPATH = 'C:\\SQLAudit' ) ;
   
   -- Enable the server audit.
   
   ALTER SERVER AUDIT MSSQL_Table_Audit WITH (STATE = ON) ;
   

2. Create Table Audit Specification MSSQL_Table_Specification which audits the update action on table Payment in the HumanResource database.

   USE HumanResource;
   
   -- Create the database audit specification.
   
   CREATE DATABASE AUDIT SPECIFICATION MSSQL_Table_Specification 
   FOR SERVER AUDIT MSSQL_Table_Audit 
   ADD (UPDATE ON Payment BY dbo ) WITH (STATE = ON) ;