SQL Server auditing should be enabled¶
Description¶
The Azure platform allows a 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. Auditing policy applied on the SQL database does not override auditing policy and settings applied on the particular SQL server where the database is hosted.
Remediation Steps¶
Azure Portal¶
Navigate to SQL Servers.
Select the SQL server.
In the left navigation in the Security section, select Auditing.
Set Auditing to On.
PowerShell¶
To enable auditing for SQL Server, get a list of all SQL servers:
Get-AzureRmSqlServer
Enable auditing for each server:
Set-AzureRmSqlServerAuditingPolicy -ResourceGroupName <resource group name> -ServerName <server name> -AuditType <audit type> -StorageAccountName <storage account name>
Azure Resource Manager¶
Ensure that a Microsoft.Sql/servers resource contains the following:
{
"properties": {
"state": "Enabled"
}
}
Example Configuration¶
Example of a Microsoft.Sql servers:
{
"type": "Microsoft.Sql/servers",
"apiVersion": "2014-04-01",
"properties": {
"state": "Enabled"
}
# other required fields here
}
Example of Microsoft.Sql servers/auditingSettings
{
"type": "Microsoft.Sql/servers/auditingSettings",
"apiVersion": "2021-05-01-preview",
"properties":{
"state": "Enabled"
}
# other required fields here
}
Terraform¶
Ensure that an azurerm_sql_server or azurerm_sql_database contains the following:
extended_auditing_policy
block exists
Example Configuration¶
resource "azurerm_sql_server" "example" {
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
retention_in_days = 90
}
# other required fields here
}