Control: 4.1.1 Ensure that 'Auditing' is set to 'On'

Description

Enable auditing on SQL Servers.

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.

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.

Remediation

From Azure Console

Go to SQL servers
For each server instance
Click on Auditing
Set Enable Azure SQL Auditing is set to On

From Powershell

Get the list of all SQL Servers

Get-AzSqlServer

For each Server, enable auditing and set the retention for at least 90 days or longer.

Log Analytics Example

Set-AzSqlServerAudit -ResourceGroupName <resource group name> -ServerName
<SQL Server name> -RetentionInDays <Number of Days to retain the audit logs,
should be 90days minimum> -LogAnalyticsTargetState Enabled -
WorkspaceResourceId "/subscriptions/<subscriptionID>/resourceGroups/insightsintegration/providers/Microsoft.OperationalInsights/workspaces/<workspacename>

Event Hub Example

Set-AzSqlServerAudit -ResourceGroupName "<resource group name>" -ServerName
"<SQL Server name>" -EventHubTargetState Enabled -EventHubName
"<Event Hub name>" -EventHubAuthorizationRuleResourceId "<Event HubAuthorization Rule Resource ID>"

Blob Storage Example*

Set-AzSqlServerAudit -ResourceGroupName "<resource group name>" -ServerName
"<SQL Server name>" -BlobStorageTargetState Enabled-StorageAccountResourceId
"/subscriptions/<subscription_ID>/resourceGroups/<Resource_Group>/providers/Microsoft.Storage/storageAccounts/<Storage Account name>"

Default Value

By default, Enable Azure SQL Auditing is set to Off.

Usage

Run the control in your terminal:

powerpipe control run azure_compliance.control.cis_v150_4_1_1

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run azure_compliance.control.cis_v150_4_1_1 --share

SQL

This control uses a named query:

select
  s.id as resource,
  case
    when audit -> 'properties' ->> 'state' = 'Disabled' then 'alarm'
    else 'ok'
  end as status,
  case
    when audit -> 'properties' ->> 'state' = 'Disabled' then name || ' auditing disabled.'
    else name || ' auditing enabled.'
  end as reason
  
  , s.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_sql_server s,
  jsonb_array_elements(server_audit_policy) audit,
  azure_subscription sub
where
  sub.subscription_id = s.subscription_id;