How to enable Auditing in Azure SQL Databases to Storage account and store logs in JSON format

In today's data-driven world, auditing is a crucial aspect of database management. It helps ensure compliance, security, and operational efficiency. Azure SQL Databases offer robust auditing capabilities, and in this blog, we'll explore how to enable auditing with a storage target in JSON format. This approach simplifies access to audit logs without the need for specialized tools like SQL Server Management Studio (SSMS).

Step 1: Enable Database-Level Auditing with Azure Monitor

The first step is to enable database-level auditing using Azure Monitor. This can be achieved through a REST API call. Here’s how you can do it:

Request:

PUT https://management.azure.com/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/test_rg/providers/Microsoft.Sql/servers/test-sv2/databases/test_db/extendedAuditingSettings/default?api-version=2021-11-01

Host: management.azure.com

Content-Length: 249

{

  "properties": {

    "state": "Enabled",

    "auditActionsAndGroups": [

      "FAILED_DATABASE_AUTHENTICATION_GROUP",

      "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",

      "BATCH_COMPLETED_GROUP"

    ],

    "isAzureMonitorTargetEnabled": true

  }

}

Explanation:

• state: Enables the auditing.
• auditActionsAndGroups: Specifies the audit groups to capture.
• isAzureMonitorTargetEnabled: Enables Azure Monitor integration.

Step 2: Create Database-Level Diagnostic Setting

Next, you need to create a diagnostic setting for storing logs in JSON format. This is done using another REST API call:

Request:

PUT https://management.azure.com/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/test_rg/providers/Microsoft.Sql/servers/test-sv2/databases/test_db/providers/Microsoft.Insights/diagnosticSettings/testDiagnosti1c?api-version=2021-05-01-preview

Content-type: application/json

Host: management.azure.com

Content-Length: 414

{

  "properties": {

    "storageAccountId": "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/test_rg/providers/Microsoft.Storage/storageAccounts/teststorage2",

    "metrics": [],

    "logs": [

      {

        "category": "sqlsecurityauditevents",

        "enabled": true,

        "retentionPolicy": {

          "enabled": false,

          "days": 0

        }

      }

    ]

  }

}

Key Details:

• storageAccountId: Specifies the target storage account.
• category: Chooses sqlsecurityauditevents to store SQL audit events.
• retentionPolicy: Retention is disabled in this configuration.

Step 3: Run a Sample Query

To generate audit logs, execute a simple query:

SELECT 1;

Step 4: Check the JSON Files in Storage Account

Finally, navigate to the Azure Storage account specified in Step 2. Open the insights-logs-sqlsecurityauditevents container and download the generated JSON file to review the audit logs.

Advantages:

• Audit logs are stored in JSON format, making them easily readable without the need for specialized tools like SQL Server Management Studio (SSMS).

Limitations:

• This approach is only applicable for database-level auditing; server-level auditing is not supported.
• Retention policy settings are not functional in this configuration.

By following this blog, you can enable auditing for Azure SQL Databases, which directly generates JSON files in an Azure Storage account. This method streamlines the access and analysis of audit data.