Learning from Expertise #1: How to audit SQL DB with PITR retention period < 35

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

We have received couple of requests from Azure customers (Standard/Premium) on how can they audit Azure SQL DB less than 35days retention period.

Adding more context into this, 7 days by default is the retention backup policy while the max can reach up to 35 days.

2020-11-30 13_10_19-Clipboard.png

First, in order to audit the noncompliant database, you can use the auditIfNotExists effect as shown in below policy

Policy Definition:

{ "parameters": { "effect": { "type": "String", "metadata": { "displayName": "Effect", "description": "PITR policy" }, "allowedValues": [ "auditIfNotExists", "Disabled" ], "defaultValue": "auditIfNotExists" } }, "policyRule": { "if": { "field": "type", "equals": "Microsoft.Sql/servers/databases" }, "then": { "effect": "[parameters('effect')]", "details": { "type": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies", "existenceCondition": { "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays", "equals": 35 } } } } } }

Policy compliance:

2020-11-30 13_40_29-[EXTERNAL] RE_ 120111125001870 _ Resolved - Message (HTML).png

In Addition, you can use DeployIfNotExists effect to audit the databases with lower retention period, Also this will allow you creating a remediation task for the noncompliant databases as below policy definition:

Policy Definition:

"parameters": { "effect": { "type": "String", "metadata": { "displayName": "Effect", "description": "Enable or disable the execution of the policy." }, "allowedValues": [ "DeployIfNotExists", "Disabled" ], "defaultValue": "DeployIfNotExists" }, "retentionDays": { "type": "String", "metadata": { "displayName": "Retention Days", "description": "Set the number of Backup Retention Days." }, "defaultValue": "35" } }, "policyRule": { "if": { "field": "type", "equals": "Microsoft.Sql/servers/databases" }, "then": { "effect": "[parameters('effect')]", "details": { "type": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies", "name": "default", "roleDefinitionIds": [ "/providers/Microsoft.Authorization/roleDefinitions/9b7fa17d-e63e-47b0-bb0a-15c516ac86ec" ], "existenceCondition": { "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays", "equals": "[parameters('retentionDays')]" }, "deployment": { "properties": { "mode": "incremental", "template": { "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#", "contentVersion": "1.0.0.0", "parameters": { "serverName": { "type": "string" }, "shortTermRetention": { "type": "string" } }, "resources": [ { "name": "[concat(parameters('serverName'),'/default')]", "type": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies", "apiVersion": "2017-10-01-preview", "properties": { "retentionDays": "[parameters('shortTermRetention')]" } } ] }, "parameters": { "serverName": { "value": "[field('fullname')]" }, "shortTermRetention": { "value": "[parameters('retentionDays')]" } } } } } } } },

Note: this policy will be require role assignment of SQL DB Contributor, Role ID: 9b7fa17d-e63e-47b0-bb0a-15c516ac86ec.

https://docs.microsoft.com/en-us/azure/role-based-access-control/built-in-roles#sql-db-contributor

I hope you find this article helpful.

Thanks to Amanda Ibrahim and Noman Qureshi for helping to create this content.

If you have any feedback please do not hesitate to provide it in the comment section below.

Ahmed S. Mazrouh

Leave a Reply Cancel reply