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

Description

It is recommended 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.

Default setting for SQL Server audit storage is disabled.

Remediation

From Console

Login to Azure console and navigate to SQL Servers.
For each server instance, go to Security section from left pane.
Click on Auditing.
Set Enable Azure SQL Auditing to On and select a storage account for log destination.
Set Retention Days setting to greater than 90 days.
Click Save.

From PowerShell

set-AzureRmSqlServerAuditing -ResourceGroupName <resource group name> - ServerName <server name> -RetentionInDays <Number of Days to retain the audit logs, should be 90days minimum>

Usage

Run the control in your terminal:

powerpipe control run azure_compliance.control.cis_v130_4_1_3

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run azure_compliance.control.cis_v130_4_1_3 --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,
  jsonb_array_elements(server_audit_policy) audit,
  azure_subscription sub
where
  sub.subscription_id = s.subscription_id;