Logo of Trend Micro

SQL Auditing Retention

Trend Micro Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 750 automated best practice checks.

Risk level: Medium (should be achieved)

Ensure that the database auditing policy attached to your Microsoft Azure SQL servers has a sufficient log data retention period, i.e. 90 days or more, configured for reliability and compliance purposes. The retention period represents the number of days to retain audit log data for the databases hosted on Azure SQL servers.

Security

A log data retention period of 90 days or more, should allow you to collect the necessary amount of audit data useful to check for anomalies and potential security breaches, or misuse of information and access to your SQL database.

Audit

To determine if your SQL database auditing policy have a sufficient log data retention period, 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 available in your Azure 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, in the Audit log destination section, click on the <Storage/Log Analytics/Event Hub> details, depending on the type of the destination used.

07 On the selected log destination configuration panel, check the value set for the Retention (Days) setting. If this value is less than 90 and different from 0 (unlimited retention), the auditing policy for the selected Azure SQL database server does not have a sufficient log data retention period configured.

08 Repeat steps no. 4 – 7 for each SQL database server provisioned 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 Get-AzSqlServer PowerShell command (cmdlet) using custom query filters to list the names of all SQL database servers (and the name of their associated resource groups) available within 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-app-server    cloud-shell-storage-westus2
cc-database-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 retention period configured for the auditing policy attached to the selected database server: 

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

04 The command output should return the requested configuration information (i.e. the number of days to retain audit log data): 

RetentionInDays
---------------
30

If the number (days) returned by the Get-AzSqlServerAuditing command output for the RetentionInDays configuration attribute is less than 90 and different from 0 (unlimited retention), as shown in the example above, the auditing policy for the selected Microsoft Azure SQL database server does not have a sufficient log data retention period currently configured.

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

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

Remediation / Resolution

To extend audit log data retention period 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, in the Audit log destination section, click on the <Storage/Log Analytics/Event Hub> details, depending on the type of the destination used.

07 On the selected log destination configuration panel, set the number of days to retain audit log data for the selected SQL server in the Retention (Days) box to 90 days or more, or use the slider control to set the right value. Click OK to apply the changes.

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

09 Repeat steps no. 3 – 8 for each subscription available 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), to extend the retention period for audit log data recorded for the selected Microsoft Azure SQL database server by using the RetentionInDays parameter. For example, the following PowerShell command request sets a retention period of 120 days for database audit logs recorded for an SQL server named "cc-sql-app-server", available in the "cloud-shell-storage-westus2" resource group (the command does not produce an output): 

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

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

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

References

Publication date Jun 12, 2019

Related Sql rules

Unlock the Remediation Steps

Gain free unlimited access
to our full Knowledge Base

Over 750 rules & best practices
for AWS and Azure

You are auditing:

SQL Auditing Retention

Risk level: Medium