Control: 4.1.1 Ensure that 'Auditing' is set to 'On'
Description
It is recommended to enable auditing on SQL Servers. 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.
Default setting for Auditing is set to Off.
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 Auditingto On and select a storage account for log destination. - Click Save.
From PowerShell
- Get the list of all SQL Servers
Get-AzureRmSqlServer
- For each Server, enable auditing.
Set-AzureRmSqlServerAuditingPolicy -ResourceGroupName <resource group name> - ServerName <server name> -AuditType <audit type> -StorageAccountName <storage account name>
Usage
Run the control in your terminal:
powerpipe control run azure_compliance.control.cis_v130_4_1_1Snapshot and share results via Turbot Pipes:
powerpipe loginpowerpipe control run azure_compliance.control.cis_v130_4_1_1 --shareSQL
This control uses a named query:
sql_server_auditing_on