Azure Policy配置SQL Database LTR参数：合规正常但部署失败求助

正在部署Azure Policy以自动配置Azure Database的LTR（长期保留）参数，目前合规审计功能正常，但自动部署功能无法生效。已查阅官方文档及相关示例仍未找到原因，之前成功为其他备份类型配置过类似策略，以下是完整的策略定义：

{
  "mode": "Indexed",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "field": "type",
          "equals": "Microsoft.Sql/servers/databases"
        },
        {
          "field": "[concat('tags[', parameters('tagName'), ']')]",
          "equals": "[parameters('tagValue')]"
        }
      ]
    },
    "then": {
      "effect": "[parameters('effect')]",
      "details": {
        "type": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies",
        "name": "default",
        "roleDefinitionIds": [
          "/providers/Microsoft.Authorization/roleDefinitions/9b7fa17d-e63e-47b0-bb0a-15c516ac86ec"
        ],
        "existenceCondition": {
          "allOf": [
            {
              "field": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies/monthlyRetention",
              "equals": "[parameters('LTRMonthlyRetention')]"
            },
            {
              "field": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies/weeklyRetention",
              "equals": "[parameters('LTRWeeklyRetention')]"
            },
            {
              "field": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies/weekOfYear",
              "equals": "[parameters('LTRWeekOfYear')]"
            },
            {
              "field": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies/yearlyRetention",
              "equals": "[parameters('LTRYearlyRetention')]"
            }
          ]
        },
        "deployment": {
          "properties": {
            "mode": "incremental",
            "template": {
              "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
              "contentVersion": "1.0.0.0",
              "parameters": {
                "serverName": {
                  "type": "string"
                },
                "LTRMonthlyRetention": {
                  "type": "string"
                },
                "LTRWeeklyRetention": {
                  "type": "string"
                },
                "LTRWeekOfYear": {
                  "type": "Integer"
                },
                "LTRYearlyRetention": {
                  "type": "string"
                }
              },
              "resources": [
                {
                  "name": "[concat(parameters('serverName'),'/default')]",
                  "type": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies",
                  "apiVersion": "2017-10-01-preview",
                  "properties": {
                    "monthlyRetention": "[parameters('LTRMonthlyRetention')]",
                    "weeklyRetention": "[parameters('LTRWeeklyRetention')]",
                    "weekOfYear": "[parameters('LTRWeekOfYear')]",
                    "yearlyRetention": "[parameters('LTRYearlyRetention')]"
                  }
                }
              ]
            },
            "parameters": {
              "serverName": {
                "value": "[field('fullname')]"
              },
              "LTRMonthlyRetention": {
                "value": "[parameters('LTRMonthlyRetention')]"
              },
              "LTRWeeklyRetention": {
                "value": "[parameters('LTRWeeklyRetention')]"
              },
              "LTRWeekOfYear": {
                "value": "[parameters('LTRWeekOfYear')]"
              },
              "LTRYearlyRetention": {
                "value": "[parameters('LTRYearlyRetention')]"
              }
            }
          }
        }
      }
    }
  },
  "parameters": {
    "effect": {
      "type": "String",
      "metadata": {
        "displayName": "Effect",
        "description": "Enable or disable the execution of the policy."
      },
      "allowedValues": [
        "DeployIfNotExists",
        "Disabled"
      ],
      "defaultValue": "DeployIfNotExists"
    },
    "LTRMonthlyRetention": {
      "type": "String",
      "metadata": {
        "displayName": "LTRMonthlyRetention",
        "description": null
      },
      "defaultValue": "3"
    },
    "LTRWeeklyRetention": {
      "type": "String",
      "metadata": {
        "displayName": "LTRWeeklyRetention",
        "description": null
      },
      "defaultValue": "3"
    },
    "LTRWeekOfYear": {
      "type": "Integer",
      "metadata": {
        "displayName": "LTRWeekOfYear",
        "description": null
      },
      "defaultValue": 3
    },
    "LTRYearlyRetention": {
      "type": "String",
      "metadata": {
        "displayName": "LTRYearlyRetention",
        "description": null
      },
      "defaultValue": "3"
    },
    "tagValue": {
      "type": "String",
      "metadata": {
        "displayName": "BackupTag Value",
        "description": "Value of the tag, such as '3'"
      },
      "defaultValue": "3"
    },
    "tagName": {
      "type": "String",
      "metadata": {
        "displayName": "Tag Name",
        "description": "Value of the tag, such as 'backup'"
      },
      "defaultValue": "backup"
    }
  }
}

核心问题排查与修正方案 #

1. 资源名称拼接错误
   部署参数中serverName使用[field('fullname')]会传入数据库的完整资源ID，导致模板中name拼接后格式完全错误。需拆分出服务器和数据库的独立名称：

   • 服务器名称：split(field('fullname'), '/')[8]
   • 数据库名称：split(field('fullname'), '/')[10]
     模板中资源名称应改为[concat(parameters('serverName'), '/', parameters('databaseName'), '/default')]，同时在部署参数中新增databaseName参数。

2. Retention参数格式不符合要求
   LTR保留期参数必须包含时间单位（如"3 months"或"3M"），纯数字"3"无法被Azure SQL API识别。需修改参数默认值为带单位的标准格式。

3. ExistenceCondition字段路径错误
   由于details中已指定子资源类型，existenceCondition中的字段路径应使用相对路径，而非完整资源类型前缀，比如将"field": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies/monthlyRetention"改为"field": "monthlyRetention"。

4. API版本建议升级
   模板中使用的2017-10-01-preview为预览版API，建议替换为稳定版2021-11-01以避免潜在兼容性问题。

修正后的完整策略示例 #

{
  "mode": "Indexed",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "field": "type",
          "equals": "Microsoft.Sql/servers/databases"
        },
        {
          "field": "[concat('tags[', parameters('tagName'), ']')]",
          "equals": "[parameters('tagValue')]"
        }
      ]
    },
    "then": {
      "effect": "[parameters('effect')]",
      "details": {
        "type": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies",
        "name": "default",
        "roleDefinitionIds": [
          "/providers/Microsoft.Authorization/roleDefinitions/9b7fa17d-e63e-47b0-bb0a-15c516ac86ec"
        ],
        "existenceCondition": {
          "allOf": [
            {
              "field": "monthlyRetention",
              "equals": "[parameters('LTRMonthlyRetention')]"
            },
            {
              "field": "weeklyRetention",
              "equals": "[parameters('LTRWeeklyRetention')]"
            },
            {
              "field": "weekOfYear",
              "equals": "[parameters('LTRWeekOfYear')]"
            },
            {
              "field": "yearlyRetention",
              "equals": "[parameters('LTRYearlyRetention')]"
            }
          ]
        },
        "deployment": {
          "properties": {
            "mode": "incremental",
            "template": {
              "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
              "contentVersion": "1.0.0.0",
              "parameters": {
                "serverName": {
                  "type": "string"
                },
                "databaseName": {
                  "type": "string"
                },
                "LTRMonthlyRetention": {
                  "type": "string"
                },
                "LTRWeeklyRetention": {
                  "type": "string"
                },
                "LTRWeekOfYear": {
                  "type": "int"
                },
                "LTRYearlyRetention": {
                  "type": "string"
                }
              },
              "resources": [
                {
                  "name": "[concat(parameters('serverName'), '/', parameters('databaseName'), '/default')]",
                  "type": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies",
                  "apiVersion": "2021-11-01",
                  "properties": {
                    "monthlyRetention": "[parameters('LTRMonthlyRetention')]",
                    "weeklyRetention": "[parameters('LTRWeeklyRetention')]",
                    "weekOfYear": "[parameters('LTRWeekOfYear')]",
                    "yearlyRetention": "[parameters('LTRYearlyRetention')]"
                  }
                }
              ]
            },
            "parameters": {
              "serverName": {
                "value": "[split(field('fullname'), '/')[8]]"
              },
              "databaseName": {
                "value": "[split(field('fullname'), '/')[10]]"
              },
              "LTRMonthlyRetention": {
                "value": "[parameters('LTRMonthlyRetention')]"
              },
              "LTRWeeklyRetention": {
                "value": "[parameters('LTRWeeklyRetention')]"
              },
              "LTRWeekOfYear": {
                "value": "[parameters('LTRWeekOfYear')]"
              },
              "LTRYearlyRetention": {
                "value": "[parameters('LTRYearlyRetention')]"
              }
            }
          }
        }
      }
    }
  },
  "parameters": {
    "effect": {
      "type": "String",
      "metadata": {
        "displayName": "Effect",
        "description": "Enable or disable the execution of the policy."
      },
      "allowedValues": [
        "DeployIfNotExists",
        "Disabled"
      ],
      "defaultValue": "DeployIfNotExists"
    },
    "LTRMonthlyRetention": {
      "type": "String",
      "metadata": {
        "displayName": "LTRMonthlyRetention",
        "description": "Retention period for monthly backups, e.g., '3 months'"
      },
      "defaultValue": "3 months"
    },
    "LTRWeeklyRetention": {
      "type": "String",
      "metadata": {
        "displayName": "LTRWeeklyRetention",
        "description": "Retention period for weekly backups, e.g., '3 weeks'"
      },
      "defaultValue": "3 weeks"
    },
    "LTRWeekOfYear": {
      "type": "int",
      "metadata": {
        "displayName": "LTRWeekOfYear",
        "description": "Week of year to take yearly backup (1-52)"
      },
      "defaultValue": 3
    },
    "LTRYearlyRetention": {
      "type": "String",
      "metadata": {
        "displayName": "LTRYearlyRetention",
        "description": "Retention period for yearly backups, e.g., '3 years'"
      },
      "defaultValue": "3 years"
    },
    "tagValue": {
      "type": "String",
      "metadata": {
        "displayName": "BackupTag Value",
        "description": "Value of the tag, such as '3'"
      },
      "defaultValue": "3"
    },
    "tagName": {
      "type": "String",
      "metadata": {
        "displayName": "Tag Name",
        "description": "Name of the tag, such as 'backup'"
      },
      "defaultValue": "backup"
    }
  }
}

内容的提问来源于stack exchange，提问作者0x35F093C756