Skip to main content

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 Policies for CIS Azure 1.5.0 for details.

Profile Applicability​

• Level 1

Description​

Configure SQL Server Audit Retention 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​

  1. Go to SQL servers
  2. For each server instance
  3. Click on Auditing
  4. If storage is selected, expand Advanced properties
  5. Ensure Retention (days) setting is greater than 90 days or 0 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 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​

  1. Go to SQL servers.
  2. For each server instance.
  3. Click Auditing.
  4. Under Audit log destination, if the destination is storage, expand Advanced properties.
  5. Set the Retention (days) setting greater than 90 days or 0 for unlimited retention.
  6. Select Save.

From Azure 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>"

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

Last updated on