SQL Audit Logging


Overview

This article explains how to successfully ingest SQL Server events (at both the server and database level) to the DefenseStorm GRID. This provides users with applicable technical controls for monitoring activity on mission critical datasets.

Use Cases

SQL Server event data provides a lot of important information that requires monitoring; see below for some examples. 

  1. Failed logins to a SQL Server
  2. Privileged use on a SQL Server
  3. Changes to a mission critical database

Assumptions 

  1. SQL Server is on-premise 
  2.  User has access to the server (to create the required audit via T-SQL or SQL Server Management Studio)
  3. SQL Server is 2016 or newer  
  4.  SQL Server has the Windows Agent installed on it

Note: These instructions and POC were based on the AWS AMI Windows 2016 Server running the developer edition of SQL Server 2017.


Example of adding an Audit file

Auditing is comprised of creating an Audit in the Server Audit Specifications folder and/or the Database Audit Specifications folder. The screenshot below provides an example of an audit that has been created within the Databases > Security > Audits folder.
 

Audit Settings

The Audit Properties window allows you to edit various audit settings. The most important is the Audit destination field - Application, Security,  or File. This determines where your audit is placed; in a binary file, on the Windows Application Log, or the Windows Security Log. Do we have a recommendation? Why is it so important?



Modify and Test json Configuration

  1. After an Audit is created,  either a Server Audit Specification or Database Audit Specification (or both) are created. How do you create the audit? 
  2. Once the settings for what items to audit and log are set, the DefenseStorm Agent ClientLogSources.json file needs to be modified. The test json file contains the following: What and how do you modify? 
     [
    {
      "comment":"SQL Server Auditing",
      "log_name":"Application",
      "source":"MSSQLSERVER"
    }
    ]


References:

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2014

https://www.sqlshack.com/understanding-sql-server-audit/