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

This article is contributed. See the original author and article here.

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.

 

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:

 

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

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.