Azure-SQLServer-Server-Auditing-Enabled

Severity : High

Description: This control ensures that SQL Server audit is enabled and configured to capture all actions and logs are retained for more than 90 days. SQL server Auditing tracks database events and writes them to an audit log in Azure storage account. It also helps you to maintain regulatory compliance, understand database activity and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations. On SQL server default auditing policy should be enabled to capture appropriate AuditActionGroups with at least 90 days of log retention. Enabling auditing at the server level ensures that all existing and newly created databases on the SQL server instance are audited. To capture all critical activities done on SQL Servers and databases within SQL servers, auditing policy should be configured to capture appropriate AuditActionGroups. Configuring SQL server auditing policy to retain logs for more than 90 days ensures that all activity logs will be available for investigating a security incident.

Remediation Steps:

Perform following to enable auditing and retention settings:

1. Login to Azure Portal using https://portal.azure.com.

2. Navigate to SQL servers.

3. Select the SQL server instance reported.

4. Select Auditing.

5. Set Auditing to On.

6. Under Audit log destination, Select Storage.

7. Select storage details to configure Storage Account and Retention Policy.

8. Set Retention (days) to greater than 90 days.

9. Select OK.

10. Select Save.

Important:

Reference:

• CIS Microsoft Azure Foundations Benchmark v1.3.0 - 02-01-2021 : Recommendation #4.1.1, 4.1.3

• 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/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2017#database-level-audit-action-groups

• https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlserverauditingpolicy?view=azurermps-6.5.0

• 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