Azure SQL Database Auditing

Azure SQL Database auditing tracks database events and writes them to an audit log in Azure storage, Azure Log Analytics, or Event Hubs. Auditing helps you maintain regulatory compliance, understand data activity, and gain insight into discrepancies and potential security threats.

Key Features

• Comprehensive Event Tracking: Capture a wide range of database actions, including data access, schema changes, and permission modifications.
• Flexible Storage Options: Store audit logs in Azure Blob Storage, Azure Log Analytics for advanced analysis, or Azure Event Hubs for real-time streaming.
• Customizable Audit Policies: Define specific groups of actions to audit based on your security and compliance requirements.
• Compliance Reporting: Facilitate compliance with regulations like GDPR, HIPAA, and SOX by providing a detailed audit trail.
• Threat Detection Integration: Seamlessly integrates with Azure SQL Database Advanced Threat Protection for a holistic security view.

How to Enable and Configure Auditing

Using the Azure Portal

The Azure portal provides an intuitive interface for configuring SQL Database auditing.

1. Navigate to your Azure SQL database or server in the Azure portal.
2. Under the Security section, select Auditing.
3. Toggle the Auditing switch to On.
4. Choose your desired Destination Type (Storage, Log Analytics, or Event Hubs).
5. Configure the destination settings (e.g., storage account, Log Analytics workspace, or Event Hub namespace).
6. Optionally, configure Storage Account Access Key or Diagnostic Settings.
7. Specify audit log retention policies.
8. Click Save.

Using Azure PowerShell

You can also manage auditing using Azure PowerShell cmdlets.

# Connect to your Azure account
Connect-AzAccount

# Set your subscription context
Set-AzContext -SubscriptionId "your-subscription-id"

# Enable auditing to Azure Storage
Set-AzSqlServerAudit -ResourceGroupName "YourResourceGroup" -ServerName "your-server-name" -AuditActionGroup "BATCH_ABORTED_GROUP", "APPLICATION_ROLE_CHANGE_PASSWORD_GROUP", "BACKUP_RESTORE_GROUP", "DATA_BASETRAIL_GROUP", "SCHEMA_CHANGE_GROUP", "SECURITY_GROUP", "SUCCESSFUL_LOGIN_GROUP", "UPDATED_SETTING_GROUP", "USER_ACCESS_CHANGE_GROUP", "INSERT_GROUP", "UPDATE_GROUP", "DELETE_GROUP", "SELECT_GROUP" -StorageEndpoint "https://yourstorageaccount.blob.core.windows.net" -RetentionInDays 30 -State Enabled

# Example of enabling auditing to Log Analytics
$logAnalytics = New-AzDiagnosticDetailSetting -Name "AuditToLogAnalytics" -Category "SQLInsights" -Metric $null -Log "AuditLogs" -ResourceId "/subscriptions/your-subscription-id/resourceGroups/YourResourceGroup/providers/Microsoft.OperationalInsights/workspaces/your-workspace-name"

Set-AzDiagnosticSetting -ResourceId "/subscriptions/your-subscription-id/resourceGroups/YourResourceGroup/providers/Microsoft.Sql/servers/your-server-name" -Settings $logAnalytics -Enabled
            

Audit Log Configuration Details

When configuring auditing, you can specify various settings:

• Audit Destination: Azure Storage, Azure Log Analytics, or Azure Event Hubs.
• Storage Account: The Azure Storage account where audit logs will be stored.
• Log Analytics Workspace: The Azure Log Analytics workspace for advanced querying and analysis.
• Event Hubs Namespace: The Azure Event Hubs namespace for real-time streaming.
• Audit Log Retention: The number of days to retain audit logs in Azure Storage.
• Audit Action Groups: Predefined categories of database events to audit. Common groups include:
  • SUCCESSFUL_LOGIN_GROUP
  • FAILED_LOGIN_GROUP
  • DATA_BASETRAIL_GROUP
  • SCHEMA_CHANGE_GROUP
  • USER_ACCESS_CHANGE_GROUP
• Advanced Auditing Policy: For finer control, you can define custom audit policies to include or exclude specific actions.

Viewing and Analyzing Audit Logs

Once auditing is enabled, you can access and analyze the audit logs:

• Azure Storage: Download the audit log files directly from your Blob Storage container. The logs are typically in CSV format.
• Azure Log Analytics: Use Kusto Query Language (KQL) to query and analyze audit data. This offers powerful analytical capabilities.
• Azure Event Hubs: Integrate with other systems or create custom dashboards to process and visualize the real-time audit data stream.

Example Kusto Query (Log Analytics)

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" and Category == "AuditLogs"
| where OperationName == "SELECT" // Example: Filter for SELECT operations
| project TimeGenerated, DatabaseName, SchemaName, TableName, User, OperationName, ClientIP
| order by TimeGenerated desc
            

Best Practices

• Enable auditing on all production SQL databases.
• Store audit logs in a separate storage account from your database for security and performance.
• Regularly review audit logs for suspicious activities.
• Set appropriate retention policies to meet compliance requirements.
• Integrate with Azure Log Analytics for advanced analysis and alerting.