Profile Applicability:

  • Level 1

Description:
 Auditing for SQL servers tracks database events and writes them to an audit log in a designated storage account. Enabling auditing at the server level ensures that all databases hosted on that server are audited for events such as logins, query executions, and any modifications to the database schema.

Rationale:
 Auditing enables the monitoring of critical database activities, which is essential for security, troubleshooting, compliance, and detecting suspicious activity. By enabling auditing at the server level, administrators ensure that both existing and newly created databases are automatically audited.

Impact:

  • Pros:

    • Provides valuable insights into database activity and potential security incidents.

    • Assists in regulatory compliance by maintaining a detailed audit trail.

    • Helps to detect discrepancies or anomalies that might indicate security issues.

  • Cons:

    • Requires additional storage for the audit logs.

    • May result in performance overhead, especially in high-traffic environments.

Default Value:

By default, Azure SQL Auditing is set to Off.

Pre-requisites:

Ensure that audit log destinations (like a storage account, EventHub, or Log Analytics) are configured and have sufficient storage capacity.

Remediation

Test Plan:

Using Azure Portal:

  1. Log in to the Azure Portal: https://portal.azure.com.

  2. Navigate to SQL servers.

  3. For each server instance, click on Auditing.

  4. Ensure that Enable Azure SQL Auditing is set to On.

Using PowerShell:

1. Get the list of all SQL Servers:

Get-AzSqlServer

2. For each server, verify that auditing is enabled:

Get-AzSqlServerAudit -ResourceGroupName <ResourceGroupName> -ServerName <SQLServerName>

3. Ensure that the BlobStorageTargetState, EventHubTargetState, or LogAnalyticsTargetState is set to Enabled.

Implementation Plan:

Using Azure Portal:

  1. Navigate to SQL servers.

  2. For each server, click on Auditing under Security.

  3. Click the toggle next to Enable Azure SQL Auditing to turn it On.

  4. Select an Audit log destination (e.g., a storage account, Event Hub, or Log Analytics).

  5. Click Save.

Using PowerShell:
 1. To enable auditing for a server, run:

Set-AzSqlServerAudit -ResourceGroupName <resource group name> -ServerName <SQLServerName> -RetentionInDays <number of days> -LogAnalyticsTargetState Enabled -WorkspaceResourceId "/subscriptions/<subscription_ID>/resourceGroups/insights-integration/providers/Microsoft.OperationalInsights/workspaces/<workspace_name>"


Backout Plan:

Using Azure Portal:

  1. Go to SQL servers.

  2. Under Auditing, disable Enable Azure SQL Auditing if necessary.

Using PowerShell:
1.  To disable auditing, run:

Set-AzSqlServerAudit -ResourceGroupName <resource group name> -ServerName <SQLServerName> -LogAnalyticsTargetState Disabled

References: