Microsoft AzureOverview
Enable auditing on SQL Servers.
Rationale
The Azure platform allows an SQL server to be created as a service. Enabling auditing at the server level ensures that all existing and newly created databases on the SQL server instance are audited. The auditing policy on the SQL database does not override the auditing policy and settings applied on the particular SQL server hosted by the database.
Auditing tracks database events and writes them to an audit log in the Azure storage account. It also helps to maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.
Default Value
By default, Auditing is set to Off.
Additional Information
- A server policy applies to all existing and newly created databases on the server.
- If server blob auditing is enabled, it always applies to the database. The database will be audited, regardless of the database auditing settings. Auditing of type table is already deprecated, leaving only type blob available.
- Enabling blob auditing on the database and enabling it on the server does not override or change any of the server blob auditing settings. Both audits will exist side by side. In other words, the database is audited twice in parallel, once by the server policy and once by the database policy.
References
- https://learn.microsoft.com/en-us/azure/security-center/security-center-enable-auditing-on-sql-servers
- https://learn.microsoft.com/en-us/powershell/module/azurerm.sql/get-azurermsqlserverauditing?view=azurermps-5.2.0
- https://learn.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlserverauditingpolicy?view=azurermps-5.2.0
- https://learn.microsoft.com/en-us/azure/sql-database/sql-database-auditing
- https://learn.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-logging-threat-detection#lt-3-enable-logging-for-security-investigation
Remediation guidance
Azure Console
- Open the SQL server using the
Open in Azurebutton - Under
Security, clickAuditing - Click the toggle for
Enable Azure SQL Auditing - Select an audit log destination and select
Save
Azure PowerShell
For each Server, enable auditing and set the retention to at least 90 days.
Log Analystics
Set-AzSqlServerAudit -ResourceGroupName <resourceGroupName> -ServerName -RetentionInDays <numberOfDays> -LogAnalyticsTargetState Enabled -WorkspaceResourceId "/subscriptions/<subscriptionID>/resourceGroups/insightsintegration/providers/Microsoft.OperationalInsights/workspaces/<workspaceName>"
Event Hub
Set-AzSqlServerAudit -ResourceGroupName "<resourceGroupName>" -ServerName "" -EventHubTargetState Enabled -EventHubName "" -EventHubAuthorizationRuleResourceId "<eventHubAuthorizationRuleResourceID>"
Blog Storage
Set-AzSqlServerAudit -ResourceGroupName "<resourceGroupName>" -ServerName "" -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/<subscriptionID>/resourceGroups/<resourceGroupName>/providers/Microsoft.Storage/storageAccounts/<storageAccountName>"
Service-wide remediation
Recommended when many resources are affected: fix the platform baseline first so new resources inherit the secure setting, then remediate the existing flagged resources in batches.
Azure
Use management group or subscription Azure Policy assignments, remediation tasks where supported, landing-zone standards, and IaC modules so drift is prevented at scale.
Operational rollout
- Fix the baseline first at the account, subscription, project, cluster, or tenant scope that owns this control.
- Remediate the currently affected resources in batches, starting with internet-exposed and production assets.
- Re-scan and track approved exceptions with an owner and expiry date.
Query logic
These are the stored checks tied to this control.
Azure SQL Servers without auditing
Connectors
Covered asset types
Expected check: eq []
{
sqlServers(where: { blobAuditingPolicies_NONE: { state: "Enabled" } }) {
...AssetFragment
}
}
