Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Auditing Improvements


By:   |   Last Updated: 2016-06-24   |   Comments (2)   |   Related Tips: More > 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.

Setup a SQL Server 2016 Security 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.

SQL Server 2016 Database Audit Specification 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.

  • Trigger to write an audit record using sp_audit_write
  • 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

    Review the historical records captured for the Production.ProductListHistory table

    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.

    Review the SQL Server 2016 Audit Logs to see that the product price increased by more than 20 percent

    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.

    Review the Audit Logs to see the Employee table was queried


    Update to the NationalIDNumber in the Employee table


    Second UPDATE to the NationalIDNumber

    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.

    Disable SQL Server 2016 Auditing


    Enable SQL Server 2016 Auditing

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

    Event Log entries indicating the auditing has been disabled


    Event Log entries indicating the auditing has been disabled and destroyed


    SQL Server 2016 Audit has been restarted

    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


    Last Updated: 2016-06-24


    next webcast button


    next tip button



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

    View all my tips
    Related Resources





    Post a comment or let the author know this tip helped.

    All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

    *Name    *Email    Email me updates 


    Signup for our newsletter
     I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



        



    Monday, April 02, 2018 - 7:56:59 AM - Nirav Back To Top

    Hello ,

    Thanks you very much. very usefull.

    I have one question that is there any way to identity the number of records updated or delated.

     

     


    Wednesday, November 30, 2016 - 9:15:39 AM - Jach Back To Top

    Great article. But I noticed you censored your user and instance name in the audit data, so I thaught I should tell you that you didn't censor it in the first picture of it.


    Learn more about SQL Server tools