lacework-global-537
4.1.1 Ensure that 'Auditing' is set to 'On' (Manual)
note
This rule has been changed to manual, see Manual Rules for CIS Azure 1.5.0 for details.
Profile Applicability
• Level 1
Description
Enable auditing on SQL Servers.
Rationale
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.
Audit
From Azure Portal
- Go to
SQL servers
- For each server instance
- Click on
Auditing
- Ensure that
Enable Azure SQL Auditing
is set toOn
From Azure Powershell
Get the list of all SQL Servers
Get-AzSqlServer
For each Server
Get-AzSqlServerAudit -ResourceGroupName <ResourceGroupName> -ServerName <SQLServerName>
Ensure that BlobStorageTargetState
, EventHubTargetState
, or LogAnalyticsTargetState
is set to Enabled
.
Remediation
From Azure Portal
- Go to
SQL servers
- For each server instance
- Click on
Auditing
- Set
Enable Azure SQL Auditing
is set toOn
From Azure Powershell
Get the list of all SQL Servers
Get-AzSqlServer
For each Server, enable auditing and set the retention for at least 90 days or longer.
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.Stora
ge/storageAccounts/<Storage Account name>"
References
https://docs.microsoft.com/en-us/azure/security-center/security-center-enable-auditing-on-sql-servers
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/get-azurermsqlserverauditing?view=azurermps-5.2.0
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlserverauditingpolicy?view=azurermps-5.2.0
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing
https://docs.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-logging-threat-detection#lt-3-enable-logging-for-security-investigation
Additional Information
A server policy applies to all existing and newly created databases on the server.
If server blob auditing is enabled, it always applies to the database. The database will be audited, regardless of the database auditing settings. Auditing type table is already deprecated leaving only type blob available.
Enabling blob auditing on the database, in addition to enabling it on the server, does not override or change any of the settings of the server blob auditing. Both audits will exist side by side. In other words, the database is audited twice in parallel; once by the server policy and once by the database policy.