Skip to main content

Azure Audit SQL Server Point In Time Restore Backup

Triage and Remediation​

Remediation​

Using Console

Sure, here are the step-by-step instructions to remediate the issue of insufficient Point-in-Time Restore (PITR) backup retention period in Azure:

  1. Log in to the Azure portal and navigate to the Recovery Services vault that contains the protected resources.

  2. In the Recovery Services vault, click on the Backup items tab from the left-hand side menu.

  3. Select the backup item for which you want to check the PITR retention period.

  4. In the Backup item's menu, click on the Backup policy option.

  5. In the Backup policy section, scroll down to the Retention tab and check the PITR retention period.

  6. If the PITR retention period is less than the required period, click on the Edit button.

  7. In the Edit backup policy window, update the PITR retention period as per your requirement.

  8. Once you have updated the PITR retention period, click on the Save button to save the changes.

  9. After saving the changes, Azure will automatically update the backup policy for the selected backup item.

  10. Repeat the same steps for all the backup items that require a longer PITR retention period.

By following these steps, you can remediate the issue of insufficient Point-in-Time Restore (PITR) backup retention period in Azure.

Using CLI

To remediate the misconfiguration related to insufficient Point In Time Restore (PITR) backup retention period in Azure, you can follow the below steps using Azure CLI:

  1. Open the Azure CLI in your preferred environment.

  2. Check the current PITR backup retention period for your Azure database by running the following command:

    az sql db show -g <resource-group-name> -s <server-name> -n <database-name> --query "pointInTimeRestoreRetentionDays"
  3. If the value returned by the above command is less than the required retention period, then update the PITR backup retention period by running the following command:

    az sql db update -g <resource-group-name> -s <server-name> -n <database-name> --backup-storage-redundancy "Geo" --point-in-time-restore --retention-days <required-retention-period>

    Replace <resource-group-name>, <server-name>, <database-name>, and <required-retention-period> with the appropriate values.

  4. After running the above command, verify that the PITR backup retention period has been updated by running the following command:

    az sql db show -g <resource-group-name> -s <server-name> -n <database-name> --query "pointInTimeRestoreRetentionDays"

    The value returned by this command should be equal to the <required-retention-period> specified in the previous step.

By following these steps, you can remediate the misconfiguration related to insufficient Point In Time Restore (PITR) backup retention period in Azure using Azure CLI.

Using Python

To remediate the misconfiguration of insufficient Point In Time Restore (PITR) backup retention period in Azure, you can use the following steps using Python:

  1. First, you need to import the necessary modules to access Azure resources using Python. You can use the azure.identity module to authenticate your Azure account and azure.mgmt.sql module to access the Azure SQL resources.
from azure.identity import DefaultAzureCredential
from azure.mgmt.sql import SqlManagementClient
  1. Next, you need to create an instance of the SqlManagementClient class by passing the Azure subscription ID and the credential object.
credential = DefaultAzureCredential()
subscription_id = 'YOUR_SUBSCRIPTION_ID'
sql_client = SqlManagementClient(credential, subscription_id)
  1. Once you have created the SqlManagementClient instance, you can use the backup_long_term_retention_policies.list_by_server method to get the backup retention policies for all the databases in the Azure SQL server.
server_name = 'YOUR_SERVER_NAME'
backup_policies = sql_client.backup_long_term_retention_policies.list_by_server(server_name)
  1. After getting the backup policies, you can iterate through them and check if the retention period is sufficient or not. If the retention period is less than the required period, you can update the policy using the backup_long_term_retention_policies.create_or_update method.
required_retention_days = 30
for policy in backup_policies:
if policy.weekly_retention is None or policy.weekly_retention < required_retention_days:
policy.weekly_retention = required_retention_days
sql_client.backup_long_term_retention_policies.create_or_update(
server_name,
policy.database_name,
policy.policy_id,
policy
)
  1. Finally, you can print a message indicating that the remediation has been completed successfully.
print("Backup retention policies have been updated successfully.")

By following these steps, you can remediate the misconfiguration of insufficient Point In Time Restore (PITR) backup retention period in Azure using Python.

Using Terraform
# Azure SQL Server (for context)
resource "azurerm_mssql_server" "this" {
name = "SQL_SERVER_NAME" # replace with your server name
resource_group_name = "RESOURCE_GROUP_NAME" # replace with your resource group
location = "AZURE_REGION" # replace with your region
version = "12.0"
administrator_login = "ADMIN_LOGIN" # replace with admin login
administrator_login_password = "ADMIN_PASSWORD" # replace with admin password

minimum_tls_version = "1.2"
}

# Azure SQL Database (PITR is configured per-database, not on the server)
resource "azurerm_mssql_database" "this" {
name = "SQL_DATABASE_NAME" # replace with your database name
server_id = azurerm_mssql_server.this.id
sku_name = "GP_Gen5_2" # replace with desired SKU
max_size_gb = 32 # adjust as needed
zone_redundant = false
}

# Configure sufficient Point-in-Time Restore (PITR) backup retention
# Set retention_days to the Cloud Conformity rule threshold:
# supported values are 1, 7, 14, 21, 28, 35
resource "azurerm_mssql_database_short_term_retention_policy" "this" {
database_id = azurerm_mssql_database.this.id
retention_days = 35 # replace with REQUIRED_PITR_DAYS from your Cloud Conformity rule
}

This change updates the backup short-term retention policy in place and does not force replacement of the database or server.

To verify, terraform plan should show an update to azurerm_mssql_database_short_term_retention_policy.this with retention_days changing from the current value (e.g., 7) to your configured value (e.g., 35), and no resources marked for replacement.