Control: 4.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days'

Description

SQL Server Audit Retention should be configured to be greater than 90 days.

Audit Logs can be used to check for anomalies and give insight into suspected breaches or misuse of information and access.

Remediation

From Azure Console

Go to SQL servers
For each server instance
Click on Auditing
If storage is selected, expand Advanced properties
Set the Retention (days) setting greater than 90 days or 0 for unlimited retention.
Select Save

From Powershell

For each Server, set retention policy for more than or equal to 90 days

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/<subscription
ID>/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 Hub
Authorization 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, SQL Server audit storage is disabled.

Usage

Run the control in your terminal:

powerpipe control run azure_compliance.control.cis_v150_4_1_6

Snapshot and share results via Turbot Pipes:

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

SQL

This control uses a named query:

select
  s.id as resource,
  case
    when (audit -> 'properties' ->> 'retentionDays')::integer = 0 then 'ok'
    when (audit -> 'properties' ->> 'retentionDays')::integer >= 90 then 'ok'
    else 'alarm'
  end as status,
  case
    when (audit -> 'properties' ->> 'retentionDays')::integer = 0 then name || ' audit retention set to unlimited days.'
    when (audit -> 'properties' ->> 'retentionDays')::integer >= 90 then name || ' audit retention greater than 90 days.'
    else  name || ' audit retention less than 90 days.'
  end as reason
  
  , s.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_sql_server s
  cross join lateral jsonb_array_elements(server_audit_policy) audit
  left join azure_subscription sub on sub.subscription_id = s.subscription_id;