SQL Audit Logging


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


  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. 

Modify and Test json Configuration

  1. After an Audit is created,  either a Server Audit Specification or Database Audit Specification (or both) are created. 
  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: 
      "comment":"SQL Server Auditing",