Azure Policy does not restrict SQL capacity changes correctly

Question

I have a custom Azure policy to restrict SQL databases to specific capacities. The environment is made up entirely of Basic & Standard tier databases - there's another (working!) policy in place to ensure this.

{
  "mode": "All",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "equals": "Microsoft.Sql/servers/databases",
          "field": "type"
        },
        {
          "field": "Microsoft.Sql/servers/databases/sku.capacity",
          "notIn": ["5", "10"]
        }
      ]
    },
    "then": {
      "effect": "deny"
    }
  }
}

However the policy blocks any scale or creation operation, regardless of if the capacity is correct or not.

Tried replacing the array with a parameter, and tried passing in the capacities as strings or integers - none make a difference, the policy always blocks the action. Targeted resources do show up in the policy compliance UI, but always non-compliant.

Answer 1

Azure Policy does not restrict SQL capacity changes correctly

I also tested your policy with the same field, but it is blocking all capacities instead of 5 and 10.

Microsoft.Sql/servers/databases/sku.capacity

Alternatively, you can use the below policy to restrict the SQL database if the capacity numbers 5 and 10 are not specified.

Here is the cmd to check the list of capacity's in specific region.

az sql db list-editions -l eastus -o table

As per your requirement, you want to allow only capacities 5 and 10. Below are the SKUs that have capacity numbers 5 and 10.

Free,Basic,S0,GP_Gen5_10,GP_S_Gen5_10,BC_Gen5_10,BC_DC_10,HS_DC_10,HS_MOPRMS_10,HS_PRMS_10,HS_Gen5_10

Azure Policy:

{
  "mode": "All",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "field": "type",
          "equals": "Microsoft.Sql/servers/databases"
        },
        {
          "not": {
            "field": "Microsoft.Sql/servers/databases/sku.name",
            "in": "[parameters('allowedSkus')]"
          }
        }
      ]
    },
    "then": {
      "effect": "deny"
    }
  },
  "parameters": {
    "allowedSkus": {
      "type": "Array",
      "metadata": {
        "displayName": "Allowed SKUs",
        "description": "The list of allowed SKUs for SQL databases."
      },
      "allowedValues": [
        "Free",
        "Basic",
        "S0",
        "GP_Gen5_10",
        "GP_S_Gen5_10",
        "BC_Gen5_10",
        "BC_DC_10",
        "HS_DC_10",
        "HS_MOPRMS_10",
        "HS_PRMS_10",
        "HS_Gen5_10"
      ]
    }
  }
}

The policy blocks the SQL Database creation if I select any capacity other than 5 and 10

The policy accepts resource creation if I select capacity numbers 5 and 10.