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": "",                 "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.



2020-11-30 13_18_36-120111125001870 - Try to obtain the backup retenti - OneNote.png

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

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.