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

Go to SQL servers.
Select the SQL server instance.
Under Security, click Auditing.
Click the toggle next to Enable Azure SQL Auditing.
Select an Audit log destination.
Click Save.

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.

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/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, Enable Azure SQL Auditing is set to Off.

Run the pipeline

To run this pipeline from your terminal:

flowpipe pipeline run azure_cis.pipeline.cis_v300_5_1_1

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_1  
}

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.