SQL Server Audit Log

1. Introduction

This is a how to guide on setting up a SQL Server Audit Log. It’s a fairly new feature introduced in SQL Server version 2008 onwards, which makes use of extended events to allow you to audit everything that happens in your server, from server setting changes all the way down to who modified a value in a specific table in the database. This information is then written as a Windows security log, Windows application log or to a flat file. It’s a good monitoring tool that can be applied in any work environment and organisation size.

2. Implementation

2.1. Setting up a SQL Server Audit

To Configure auditing in SQL Server Perform the following steps:

1. Open SQL server Management Studio connect to your SQL server instance and expand the Security Folder

2. Right click on the Audits folder and select New Audit

3. Configure the audit properties as shown below

a. Audit Name

b. File Destination

4. Right-click on the audit that you have just created and enable it.

5. Right-click on the Server Audit Specifications folder and select New Server Audit Specification

6. Configure the server audit specification to audit the following action types as shown below

7. Right-click on the server audit specification that you have just created and enable it.

8. Expand the security folder in database that you want to audit

9. Right-click on the Database Audit Specification folder and select New Database Audit Specification

10. Configure the database audit specification to audit the following types

11. Right-click on the server audit specification that you have just created and enable it.

2.2. Querying the Log

In order to query the log, navigate yourself to the Server Security folder under the folder Audits. Expand it and right-click on the Audit Log and click ‘View Audit Logs’

This provides you with a user interface which shows all the actions that your database users take. However, the audit has it’s flaws and is not ideal. Querying the .log file can be extremely time consuming and filtering the log file with a SQL Statement proves inefficient.

NB! The Query Used to Query the Log Files is

SELECT event_time
,sequence_number
,action_id
,server_principal_name
,server_instance_name
,database_name
,schema_name
,object_name
,statement
FROM sys.fn_get_audit_file('C:\SQLData\MSSQL12.MSSQLSERVER\MSSQL\Audits\*.*', DEFAULT, DEFAULT)

In order to resolve those issues, a solution was put in place that would take the audit .log file and put it into a SQL Server table. That way you can set up multiple audits and make them point at the same table, so you can find out what’s happening on all databases not just in one.

1. Step 1 Create Audit Table

CREATE TABLE [dbo].[tbl_SQLAudit_History]
(event_time DATETIME
, sequence_number INT
, action_id VARCHAR(10)
, server_principal_name VARCHAR(255)
, server_instance_name VARCHAR(255)
, database_name VARCHAR(255)
, schema_name VARCHAR(255)
, object_name VARCHAR(500)
, statement VARCHAR(MAX)
);

2. Second step is to make our Audit Log File smaller which can be done at it’s properties

NB! Specifying a small log file means that it’ll get filled up really quickly. We need to run a job against the database that will archive this file and keep reinserting records in a SQL Table.

3. Stored Procedure

CREATE PROCEDURE [dbo].[sp_SqlAuditCaptureAuditLogs]
AS
BEGIN
SET XACT_ABORT ON;
EXEC xp_cmdshell 
'powershell.exe "Move-Item C:\SQLData\MSSQL12.MSSQLSERVER\MSSQL\Audits\*.sqlaudit C:\SQLData\MSSQL12.MSSQLSERVER\MSSQL\Audits\SQLAuditLogs_Staging -ErrorAction SilentlyContinue"', 
no_output;
DECLARE @TableSize TABLE(TotalSizeIN_MB NUMERIC(36, 2));
INSERT INTO @TableSize
SELECT Total_MB
FROM
(
SELECT s.Name AS SchemaName
, t.Name AS TableName
, p.rows AS RowCounts
, CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB
, CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB
, CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
 INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE(1 = 1)
AND s.name = 'dbo'
AND t.name = 'tbl_SQLAudit_History'
GROUP BY t.Name
, s.Name
, p.Rows
) AS MainData;
-- Add Results in a variable
DECLARE @TotalTableSize NUMERIC(36, 2)=
(
SELECT TotalSizeIN_MB
FROM @TableSize
);
-- IF TableTotalSize > 1024.00 MB (1 GB) THEN TRUNCATE OTHERWISE JUST SELECT
IF @TotalTableSize > '1024.00'
TRUNCATE TABLE [dbo].[tbl_SQLAudit_History];
ELSE
INSERT INTO [dbo].[tbl_SQLAudit_History]
(event_time
, sequence_number
, action_id
, server_principal_name
, server_instance_name
, database_name
, schema_name
, object_name
, statement
)
SELECT event_time
, sequence_number
, action_id
, server_principal_name
, server_instance_name
, database_name
, schema_name
, object_name
 , statement
FROM sys.fn_get_audit_file('C:\SQLData\MSSQL12.MSSQLSERVER\MSSQL\Audits\*.*', DEFAULT, DEFAULT);
EXEC xp_cmdshell 
'powershell.exe "Remove-Item C:\SQLData\MSSQL12.MSSQLSERVER\MSSQL\Audits\*.* -ErrorAction SilentlyContinue"', 
no_output;
END;
GO

This Stored Procedure is meant to insert records periodically and archive the Audit file.

4. Schedule the procedure as a job

5. Query the table to test it

SELECT DISTINCT event_time
, sequence_number
, action_id
, server_principal_name
, server_instance_name
, database_name
, schema_name
, object_name
, statement
FROM tbl_SQLAudit_History
WHERE (1 = 1)
AND server_principal_name like '%Yovcho%'
AND action_id = 'SL'

3. Summary

The solution I have put in place provides us with the opportunity to monitor multiple databases from multiple database audit specifications and puts them into a single table. Remember to specify exactly which users you would like to monitor on a database level.