Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Check for Sufficient Point in Time Restore (PITR) Backup Retention Period

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

Risk Level: Medium (should be achieved)
Rule ID: Sql-015

Ensure that Microsoft Azure SQL databases have a sufficient Point in Time Restore (PITR) backup retention period configured for security and compliance purposes. Azure SQL service automatically creates database backups that are kept between 7 and 35 days. The SQL service uses Azure read-access geo-redundant storage (RA-GRS) to ensure that the backups are preserved even if the primary datacenter becomes unavailable. Prior to running this rule by the Cloud Conformity engine, the PITR backup retention period must configured within the rule settings, on the Cloud Conformity account dashboard. The supported values are: 1, 7, 14, 21, 28 and 35 days. Azure default backup short term retention days setting is 7.

This rule resolution is part of the Conformity Security & Compliance tool for Azure.

Reliability

Having an optimal backup retention period set for Azure SQL databases will enforce your backup strategy to follow the best practices as specified in the compliance regulations promoted within your organization. Retaining point-in-time SQL database backups for a longer period of time will allow you to handle more efficiently your data restoration process in the event of a failure.

Audit

To determine if your Azure SQL databases have a sufficient PITR backup retention period configured, perform the following actions:

Using Azure Console

01 Sign in to your Cloud Conformity account, access Check for Sufficient Point in Time Restore (PITR) Backup Retention Period conformity rule settings and note the PITR backup retention period configured for the rule.

02 Sign in to Azure Management Console.

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

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

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

06 On the navigation panel, under Settings, select Manage backups to access the backup retention policies created for the SQL databases available within the selected server.

07 On the Manage backups page, check the value (i.e. number of days) available in the PITR BACKUPS column for each SQL database listed. If the Point in Time Restore (PITR) backup retention period set for the verified SQL database(s) is different than the retention period identified at step no. 1, the selected Microsoft Azure SQL database server does not have a sufficient backup retention period configured for its databases.

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

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

Using Azure PowerShell

01 Sign in to your Cloud Conformity account, access Check for Sufficient Point in Time Restore (PITR) Backup Retention Period conformity rule settings and note the PITR backup retention period configured for the rule.

02 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

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

ServerName             ResourceGroupName
----------             -----------------
cc-webapp-sql-server   cloud-shell-storage-westeurope
cc-project5-db-server  cloud-shell-storage-westeurope

04 Run Get-AzSqlDatabase PowerShell command using the name of the SQL server that you want to examine as identifier parameter and custom query filters to get the name of each SQL database available within the selected server: 

Get-AzSqlDatabase -ResourceGroupName cloud-shell-storage-westeurope -ServerName cc-webapp-sql-server | Select-Object DatabaseName

05 The command output should return the list with the requested SQL database names: 

DatabaseName
------------
cc-web-sql-database
master

06 Run Get-AzSqlDatabaseBackupShortTermRetentionPolicy PowerShell command using the name of the SQL database that you want to examine as identifier parameter and custom query filters to get the PITR backup retention period configured for the selected database: 

Get-AzSqlDatabaseBackupShortTermRetentionPolicy -ResourceGroupName cloud-shell-storage-westeurope -ServerName cc-webapp-sql-server -DatabaseName cc-web-sql-database | Select-Object RetentionDays

07 The command output should return the database backup retention period in number of days: 

RetentionDays
-------------
            7

If the value returned by the Get-AzSqlDatabaseBackupShortTermRetentionPolicy cmdlet output is different than the retention period identified at step no. 1, the selected Microsoft Azure SQL database server does not have a sufficient backup retention period configured for its databases.

08 Repeat steps no. 4 – 7 for each SQL database server created within the current subscription.

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

Remediation / Resolution

To configure the right Point in Time Restore (PITR) backup retention period for your Microsoft Azure SQL database servers, perform the following actions:

Using Azure Console

01 Sign in to your Cloud Conformity account, access Check for Sufficient Point in Time Restore (PITR) Backup Retention Period conformity rule settings and copy the PITR backup retention period configured for the rule.

02 Sign in to Azure Management Console.

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

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

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

06 On the navigation panel, under Settings, select Manage backups to access the backup retention policies created for the SQL databases available within the selected server.

07 On the Manage backups page, select the SQL database that you want to reconfigure (see Audit section part I to identify the right database), then click on Configure retention button to open the retention policy set for the selected database.

08 On the Configure policies panel, select the value copied at step no. 1 from the Point In Time Restore Configuration dropdown list to set the right PITR backup retention period, in number of days, for the selected Microsoft Azure SQL database. Click Apply to save the configuration changes.

09 Repeat step no. 7 and 8 for other SQL databases available within the selected SQL server.

10 Repeat steps no. 5 – 9 for each SQL server provisioned in the selected Azure subscription.

11 If required, repeat steps no. 4 – 10 for each subscription created in your Microsoft Azure cloud account.

Using Azure PowerShell

01 Sign in to your Cloud Conformity account, access Check for Sufficient Point in Time Restore (PITR) Backup Retention Period conformity rule settings and copy the PITR backup retention period configured for the rule.

02 Run Set-AzSqlDatabaseBackupShortTermRetentionPolicy PowerShell command using the name of the SQL database that you want to reconfigure as identifier parameter (see Audit section part II to identify the right resource), to set the right PITR backup retention period, as copied at step no. 1, for the selected Microsoft Azure SQL database. For example, the following Set-AzSqlDatabaseBackupShortTermRetentionPolicy command request sets the database backup retention period to 35 days: 

Set-AzSqlDatabaseBackupShortTermRetentionPolicy -ResourceGroupName cloud-shell-storage-westeurope -ServerName cc-webapp-sql-server -DatabaseName cc-web-sql-database -RetentionDays 35

03 The command output should return the PowerShell cmdlet request metadata: 

ResourceGroupName               ServerName            DatabaseName          RetentionDays
-----------------               ----------            ------------          -------------
cloud-shell-storage-westeurope  cc-webapp-sql-server  cc-web-sql-database              35

04 Repeat step no. 2 and 3 for other SQL databases created within the selected SQL server.

05 Repeat steps no. 2 – 4 for each SQL server provisioned in the selected Azure subscription.

06 If required, repeat steps no. 2 – 5 for each subscription created in your Microsoft Azure cloud account.

References

Publication date Oct 26, 2019

Related Sql rules