lacework-global-541
4.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days' (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
SQL Server Audit Retention should be configured to be greater than 90 days.
Rationale
Audit Logs can be used to check for anomalies and give insight into suspected breaches or misuse of information and access.
Audit
From Azure Portal
- Go to
SQL servers
- For each server instance
- Click on
Auditing
- If storage is selected, expand
Advanced properties
- Ensure
Retention (days)
setting is greater than90
days or0
for unlimited retention.
From Azure Powershell
Get the list of all SQL Servers
Get-AzSqlServer
For each Server
Get-AzSqlServerAudit -ResourceGroupName <resource group name> -ServerName <server name>
Ensure that RetentionInDays
is set to more than or equal to 90
note
If the SQL server is set with LogAnalyticsTargetState
setting set to Enabled
, run the following additional command.
Get-AzOperationalInsightsWorkspace | Where-Object {$_.ResourceId -eq <SQL Server WorkSpaceResourceId>}
Ensure that RetentionInDays
is set to more than or equal to 90
Remediation
From Azure Portal
- Go to
SQL servers
- For each server instance
- Click on
Auditing
- If storage is selected, expand
Advanced properties
- Set the
Retention (days)
setting greater than90
days or0
for unlimited retention. - Select
Save
From Azure Powershell
For each Server, set retention policy for more than or equal to 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.Stora
ge/storageAccounts/<Storage Account name>"
References
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing
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-azurermsqlserverauditing?view=azurermps-5.2.0
https://docs.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-logging-threat-detection#lt-6-configure-log-storage-retention