SQL Server 2016 Auditing Improvements

By: Rajendra Gupta   |   Comments (5)   |   Related: > SQL Server 2016

Problem

Auditing is an important mechanism for many organizations to serve as a checks and balances.  In SQL Server 2016 are there any new Auditing features to support the business and simplify how we audit?  Check out this tip to learn more.

Solution

The purpose of SQL Server database auditing is to audit database level activities such as INSERT, UPDATE, DELETE and even data access via the SELECT command. SQL Server 2016 provides the following new features for database auditing:

• User - Defined Audit
• Audit Filtering
• Audit Resilience

Let's see how to implement each of these features in SQL Server 2016.

SQL Server 2016 User Defined Audit

In SQL Server 2016 we can configure custom logic and events to audit.  Below are the steps to configure user defined audit:

USE [master]
GO

CREATE SERVER AUDIT [Audit_User_Defined_Test]
TO FILE 
( FILEPATH = N'C:\mssqltips\Audit'
 ,MAXSIZE = 100 MB
 ,MAX_ROLLOVER_FILES = 2147483647
 ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
)
GO

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

We can use SQL Server Management Studio to configure a Server Audit by navigating to the Security folder then right clicking on Audit-> Create New Audit.

SQL Server 2016 Database Level Audit

Below is code to create a SQL Server 2016 Database Level Audit:

Use [AdventureWorks2016CTP3]
GO

Create Database Audit Specification Test_database_audit
for server audit [Audit_User_Defined_Test]
ADD (User_Defined_Audit_Group)
With(State=ON)
GO

We can view the database audit properties by expanding the Adventureworks database and go to Security then Database Audit Specifications, then right click on Audit properties.

We can see that the Audit action type is set to User_Defined_Audit_Group which basically tracks events raised by the sp_audit_write stored procedure.

Suppose we want to audit the Adventureworks database table [Production].[ProductListPriceHistory] which is used to store the price of all the products and their history records. Sales people previously modify the price of the products based on the requirements however we want to audit if anyone has reduced the price by more than 20%.

Now we will write a trigger to check this condition and write the data to audit files.

Create Trigger [Production].[ProductListPrice] 
on [Production].[ProductListPriceHistory]
After Update
As
declare @OldListPrice money
,@NewListPrice money,
@productId int,
@msg nvarchar(2500)
select @OldListPrice=d.ListPrice
from deleted d
select @NewListPrice= i.ListPrice , @productId=i.ProductId
from inserted i

If (@OldListPrice*0.80 >@NewListPrice)  -- implement logic condition
begin
 Set @msg='Product '+ Cast (@productid as varchar(50))+' ListPrice is decreased by more than 20%' --print message to be logged
 Exec sp_audit_write @user_defined_event_id=27,
 @succeeded =1, 
 @user_defined_information = @msg;
End
GO

Now we will update the price of the product and see if this is logged into the events

Now if we go to View audit logs in SQL Server Management Studio under Security->Audit->Audit_User_Defined_Test, we can see the price changed meet our trigger condition and it is recorded in Audit logs.

As we can see in the logs it gives detailed information with database name, server name, object name, statement along with lot of useful information.  Similarly we can configure the audit as per our requirement and this could be quite useful functionality.

SQL Server 2016 Audit Filtering

Audit filtering allows the filtering of unwanted audit events before they are written to the audit log. As part of an auditing policy, we can log which users are accessing selected tables that contain sensitive data.

Suppose we want to audit the Employee information table for SELECT and UPDATE statements, for this we will create new server audit and database audit as shown below:

Use Master --server Audit needs to be created in the Master database
Create Server Audit Audit_Security_Employee
To file (FilePath='C:\mssqltips\Audit');
GO

Alter Server Audit Audit_Security_Employee with(STATE=ON)
GO

Use [AdventureWorks2016CTP3]
GO

Create Database Audit Specification Audit_Employee
for Server Audit Audit_Security_Employee
Add (Select , update  on [HumanResources].[Employee]  by dbo) --Insert condition for which event to be tracked.
With (State=ON);
GO

Now let's execute both SELECT and UPDATE operations to track the audit events.

SELECT * 
FROM [HumanResources].[Employee]
GO

UPDATE [HumanResources].[Employee] 
SET NationalIDNumber=987654321 
WHERE BusinessEntityID=1
GO

Now we will view the audit logs and see if it is logged.

As we can see in the images above both the events are logged in the audit logs.  This could be also very useful if we want to see who has accessed the particular data with detailed information logged into event logs.

SQL Server 2016 Audit Resilience

SQL Server 2016 Audit Resilience gives us the ability to recover auditing data from a temporary file in case network issues occur, this will ensure audit logs are not lost during failover. SQL Server will automatically recover the data in such situations.

The next item to note is that SQL Server 2016 also audits all enable and disable events.

To check this functionality, disable the audit feature and then enable if after some time.

Now we will review the Audit logs to verify the events are captured.

We can see here that both the Disabled Audit and Enable Audit events are logged into the Audit Logs. There are 2 entries for the disable audit as well as disable and destroy audit which basically means audit has stopped logging into the audit logs.

Next Steps

• Be sure to keep this new SQL Server 2016 auditing functionality in mind as you upgrade.  These are steps in the right direction.
• Check out all of the SQL Server 2016 tips.
• Read more about SQL Server Auditing and Compliance.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips