Enable Auditing for SQL Servers

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features

Knowledge Base Microsoft Azure Sql Enable Auditing for SQL Servers

Last updated: 03 February 2020

Risk level: Medium (should be achieved)

Ensure that the "Auditing" feature is enabled within your Microsoft Azure SQL server configuration settings in order to monitor your SQL databases for security, compliance and troubleshooting purposes. Microsoft Azure allows an SQL server to be created as a service. Enabling auditing at the server level ensures that all existing and newly created databases on that SQL server are audited.

Security

Once enabled, the SQL database auditing starts recording database events and writes them to an audit log created within your Azure Storage account, OMS workspace or Event Hub. The logging data recorded can be extremely useful for maintaining security and regulatory compliance, understand database activity and trends, and gain insight into anomalies that could indicate potential security violations.

Audit

To determine if database auditing is enabled for your Microsoft Azure SQL servers, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL server to list only the SQL database servers currently available in your account.

04 Click on the name of the SQL server that you want to examine.

05 In the navigation panel, under Security, select Auditing to access the auditing policy configuration settings for the selected database server.

06 On the auditing policy configuration page, check the Auditing status. If Auditing configuration setting is set to OFF, the "Auditing" feature is not enabled for the selected Microsoft Azure SQL database server.

07 Repeat steps no. 4 – 6 for each SQL database server provisioned within the selected subscription.

08 Repeat steps no. 3 – 7 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI and PowerShell

01 Run Get-AzSqlServer PowerShell cmdlet using custom query filters to list the names of all SQL database servers (and their associated resource groups) available in the current Azure subscription:

Get-AzSqlServer | Select-Object ServerName,ResourceGroupName

02 The command output should return the requested SQL database server information:

ServerName           ResourceGroupName
----------           -----------------
cc-sql-db-server     cloud-shell-storage-westus2
cc-project5-server   cloud-shell-storage-westus2

03 Run Get-AzSqlServerAuditing PowerShell command using the name of the SQL server that you want to examine as identifier parameter and custom query filters to get the "Auditing" feature configuration status for the selected database server:

Get-AzSqlServerAuditing -ServerName "cc-sql-db-server" -ResourceGroupName "cloud-shell-storage-westus2" | Select-Object AuditState

04 The command output should return the requested feature status:

AuditState
----------
Disabled

If Get-AzSqlServerAuditing cmdlet output returns Disabled for the AuditState configuration attribute, as shown in the example above, the database auditing is not enabled for the selected Microsoft Azure SQL database server.

05 Repeat step no. 3 and 4 for each SQL database server provisioned in the selected subscription.

06 Repeat steps no. 1 – 5 for each subscription created in your Microsoft Azure cloud account.

Remediation / Resolution

To enable SQL database auditing for your Microsoft Azure SQL database servers, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL server to list only the SQL database servers provisioned in your Azure account.

04 Click on the name of the SQL server that you want to reconfigure.

05 In the navigation panel, under Security, select Auditing to access the auditing policy configuration settings for the selected SQL server.

06 On the auditing policy configuration page, under Auditing, select ON and choose at least one log destination to write the audit log files generated for the selected database server. You can configure the "Auditing" feature to write logs to an Azure storage account, to a Log Analytics workspace for consumption by Azure Monitor logs, or to Azure Event Hub for consumption using an event hub. You can set up any combination of the following options, and your database audit logs will be written to each:

To configure writing SQL database audit logs to an Azure storage account, select Storage and click Configure under Storage details. Select the Azure storage account where the log files will be saved, the appropriate retention period, then click OK to apply the changes.
To configure writing SQL audit logs to a Log Analytics workspace, select Log Analytics (Preview) and click Configure under Log Analytics details. Select or create the Log Analytics workspace where the log files will be written, then click OK to apply the configuration changes. Be aware that enabling auditing to Log Analytics will incur cost based on ingestion rates.
To configure writing SQL database audit logs to an event hub, select Event Hub (Preview) and click Configure under Event Hub details. Select the event hub where your SQL server logs will be written, then click OK to apply the changes. Make sure that the event hub is in the same region as your SQL database server.

07 Click Save to enable auditing for the selected SQL server.

08 Repeat steps no. 4 – 7 for each SQL database server available in the selected subscription.

09 Repeat steps no. 3 – 8 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI and PowerShell

01 Run Set-AzSqlServerAuditing PowerShell cmdlet using the name of the SQL server that you want to reconfigure as identifier parameter (see Audit section part I to identify the right Azure resource) and the log destination where the audit log files will be saved, to enable auditing for the selected Microsoft Azure SQL database server. The audit log destination is determined by specifying one of the following parameters: BlobStorage, LogAnalytics or EventHub (if none is specified, the default is BlobStorage). Use the State parameter to enable/disable the auditing policy. When the audit log destination is blob storage, specify the StorageAccountName parameter to configure the storage account that will hold the audit logs. You can also define retention for the audit logs by setting the right value for the RetentionInDays parameter. For example, the following PowerShell command request enables database auditing for an SQL server named "cc-sql-db-server", available in the "cloud-shell-storage-westus2" resource group, that saves the generated audit log files for 90 days to blob storage within an Azure Storage Account named "abcd1234abcd1234abcd1234" (the command does not produce an output):

Set-AzSqlServerAuditing -State Enabled -ServerName "cc-sql-db-server" -ResourceGroupName "cloud-shell-storage-westus2" -StorageAccountName "abcd1234abcd1234abcd1234" -RetentionInDays 90

02 Repeat step no. 1 for each SQL database server available within the selected subscription.

03 Repeat step no. 1 and 2 for each subscription created in your Microsoft Azure cloud account.

References

Azure Command Line Interface (CLI) Documentation
SQL
Get-AzSqlServer
Get-AzSqlServerAuditing
Set-AzSqlServerAuditing

Publication date Jul 24, 2019

Enable Auditing for SQL Servers

Audit

Using Azure Console

Using Azure CLI and PowerShell

Remediation / Resolution

Using Azure Console

Using Azure CLI and PowerShell

References

Related Sql rules