This blog will walk you through enabling auditing and storing logs in JSON format, simplifying access and analysis without the need for specialized tools
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.
Microsoft