Pipeline: 5.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 Portal

Go to SQL servers.
For each SQL server, under Security, click Auditing.
If Storage is checked, expand Advanced properties.
Set Retention (days) to a value greater than 90, or 0 for unlimited retention.
Click Save.

From PowerShell

For each Server, set retention policy to more than 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 more than 90 days> -LogAnalyticsTargetState Enabled -WorkspaceResourceId "/subscriptions/<subscription ID>/resourceGroups/insights-integration/providers/Microsoft.OperationalInsights/workspaces/<workspace name>

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.

Run the pipeline

To run this pipeline from your terminal:

flowpipe pipeline run azure_cis.pipeline.cis_v300_5_1_6

Use this pipeline

To call this pipeline from your pipeline, use a step:

step "pipeline" "step_name" {
  pipeline = azure_cis.pipeline.cis_v300_5_1_6  
}

Params

Name	Type	Required	Description	Default
database	connection.steampipe	Yes	Database connection string.	`connection.steampipe.default`
notifier	notifier	Yes	The name of the notifier to use for sending notification messages.	`notifier.default`
notification_level	string	Yes	The verbosity level of notification messages to send. Valid options are 'verbose', 'info', 'error'.	`info`

Outputs

This pipeline has no outputs.