Description:

Enable auditing on SQL Servers.Auditing helps to maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations. 


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. The 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.

Impact:

Server auditing is important for security, but it also helps keep operations running at your company. ... With a defined audit policy, administrators can track changes or attempts to access critical information through SQL Server auditing.


Default Value:

By default, Auditing is set to Off.


Audit:

From Azure Console

  1. Go to SQL servers

  2. For each server instance

  3. Click on Auditing

  4. Ensure that Auditing is set to On


Using Azure PowerShell

Get the list of all SQL Servers

Get-AzureRmSqlServer


For each Server

Get-AzureRmSqlServerAuditing -ResourceGroupName <resource group name> - ServerName <server name>

Ensure that AuditState is set to Enabled.


Remediation:

From Azure Console

  1. Go to SQL servers

  2. For each server instance

  3. Click on Auditing

  4. Set Auditing to On


Backout Plan:

  1. Go to SQL servers

  2. For each server instance

  3. Click on Auditing

  4. Ensure that Auditing is set to Off


Using Azure 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>


References:

  1. https://docs.microsoft.com/en-us/azure/security-center/security-center-enable- auditing-on-sql-servers

  2. https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/get- azurermsqlserverauditing?view=azurermps-5.2.0

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

  4. Azure SQL Auditing for Azure SQL Database and Azure Synapse Analytics - Azure SQL Database 

  5. https://docs.microsoft.com/en-us/azure/security/benchmarks/security-controls- v2-logging-threat-detection#lt-4-enable-logging-for-azure-resources