由于策略的原因，在Azure上创建SQL Server失败

Question

我们正在使用ARM模板创建SQL Server，但由于存在策略，因此无法创建SQL Server，以下是2个策略例外：

策略不允许##errorResource 'xxxxx‘。错误类型: PolicyViolation，策略定义名称: SQL Server应使用虚拟网络服务终结点(NPD)，策略分配名称: NPD1a8a9dc8-aef3-421a-93。错误类型: PolicyViolation，策略定义名称:应启用对SQL server的审核(NPD)，策略分配名称: NPD7885d0ef-a3de-44a3-9a。

以下是我们正在使用的ARM模板，我不确定它为什么会失败，因为现在我们已经在SQL Server创建过程中启用了VNet规则和审计：

{
  "$schema": http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#,
  "contentVersion": "1.0.0.0",
  "parameters": {
    "serverName": {
      "type": "string",
      "metadata": {
        "description": "The name of the new database server to create."
      }
    },
    "location": {
      "type": "string",
      "metadata": {
        "description": "The location of the database server."
      }
    },
    "serverVersion": {
      "type": "string",
      "defaultValue" : "12.0"
    },
    "administratorLogin": {
      "type": "string",
      "metadata": {
        "description": "The account name to use for the database server administrator."
      }
    },
    "administratorLoginPassword": {
      "type": "securestring",
      "metadata": {
        "description": "The password to use for the database server administrator."
      }
    },
    "storageAccountName": {
      "type": "string",
      "metadata": {
        "description": "The name of the new storage account to create."
      }
    },
    "emailAddresses": {
      "type": "string",
      "defaultValue": "",
      "metadata": {
        "description": "Email address for alerts."
      }
    },
    "privateEndpointName": {
      "type": "string"
    },
    "vnetName": {
      "type": "string"
    },
    "vnetRg": {
          "type": "string",
          "metadata": {
              "description": "Resource Group Name of VNet"
          }
    },
    "subnet1Name": {
      "type": "string"
    },
    "storageType": {
      "type": "string",
      "defaultValue": "Standard_GRS",
      "allowedValues": [
        "Standard_LRS",
        "Standard_ZRS",
        "Standard_GRS",
        "Standard_RAGRS",
        "Premium_LRS"
      ]
    },
    "BUSINESS-OWNER": {
        "type": "string"
    },
    "COST-CENTER": {
        "type": "int"
    },
    "LIFECYCLE": {
        "type": "string"
    },
    "APPLICATION": {
      "type": "string"
    },
    "PROJECT-CODE": {
      "type": "string"
    },
    "TECHNICAL-OWNER": {
      "type": "string"
    },
    "GL-CODE": {
      "type": "string"
    } 
  },
  "resources": [
    {
      "name": "[parameters('serverName')]",
      "type": "Microsoft.Sql/servers",
      "location": "[parameters('location')]",
      "apiVersion": "2014-04-01-preview",
      "properties": {
        "administratorLogin": "[parameters('administratorLogin')]",
        "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
        "version": "[parameters('serverVersion')]",
        "minimalTlsVersion": "1.2",
        "publicNetworkAccess": "Disabled"
      },
      "tags": {
        "BUSINESS-OWNER": "[parameters('BUSINESS-OWNER')]",
        "COST-CENTER": "[parameters('COST-CENTER')]",
        "LIFECYCLE": "[parameters('LIFECYCLE')]",
        "APPLICATION": "[parameters('APPLICATION')]",
        "PROJECT-CODE": "[parameters('PROJECT-CODE')]",
        "TECHNICAL-OWNER": "[parameters('TECHNICAL-OWNER')]",
        "GL-CODE": "[parameters('GL-CODE')]"
      },
      
      "resources": [
        {
          "name": "sergiodb1",
          "type": "databases",
          "location": "[parameters('location')]",
          "tags": {
          "BUSINESS-OWNER": "[parameters('BUSINESS-OWNER')]",
          "COST-CENTER": "[parameters('COST-CENTER')]",
          "LIFECYCLE": "[parameters('LIFECYCLE')]",
          "APPLICATION": "[parameters('APPLICATION')]",
          "PROJECT-CODE": "[parameters('PROJECT-CODE')]",
          "TECHNICAL-OWNER": "[parameters('TECHNICAL-OWNER')]",
          "GL-CODE": "[parameters('GL-CODE')]"
        },
          "apiVersion": "2015-05-01-preview",
          "dependsOn": [
            "[parameters('serverName')]"
          ],
          "properties": {
            "edition": "Basic",
            "collation": "SQL_Latin1_General_CP1_CI_AS"
          }
        },
        {
          "type": "Microsoft.Sql/servers/virtualNetworkRules",
          "apiVersion": "2020-08-01-preview",
          "name": "[concat(parameters('serverName'), '/allow-', parameters('subnet1Name'))]",
          "dependsOn": [ "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]" ],
          "properties": {
              "virtualNetworkSubnetId": "[resourceId(parameters('vnetRg'), 'Microsoft.Network/virtualNetworks/subnets', parameters('vnetName'), parameters('subnet1Name'))]",
              "ignoreMissingVnetServiceEndpoint": false
          }
        },
        {
          "apiVersion": "2014-04-01-preview",
          "type": "firewallrules",
          "location": "[parameters('location')]",
          "name": "AllowAllWindowsAzureIps",
          "dependsOn": [
            "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
          ],
          "properties": {
            "endIpAddress": "0.0.0.0",
            "startIpAddress": "0.0.0.0"
          }
        },
        {
          "name": "Default",
          "type": "auditingSettings",
          "apiVersion": "2017-03-01-preview",
          "location": "[parameters('location')]",
          "dependsOn": [
            "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
          ],
          "properties": {
            "State": "Enabled",
            "storageEndpoint": "[concat('https://',parameters('storageAccountName'),'.blob.core.windows.net')]",
            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
            "retentionDays": 365,
            "auditActionsAndGroups": null,
            "storageAccountSubscriptionId": "[subscription().subscriptionId]",
            "isStorageSecondaryKeyInUse": false,
            "isAzureMonitorTargetEnabled": false
          }
        },
        {
          "name": "DefaultSAP",
          "type": "securityAlertPolicies",
          "apiVersion": "2017-03-01-preview",
          "dependsOn": [
            "[concat('Microsoft.Sql/servers/', parameters('serverName'))]",
            "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/auditingSettings/Default')]"
          ],
          "properties": {
            "state": "Enabled",
            "disabledAlerts": null,
            "emailAddresses": "[array(parameters('emailAddresses'))]",
            "emailAccountAdmins": true,
            "storageEndpoint": "[concat('https://',parameters('storageAccountName'),'.blob.core.windows.net')]",
            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
            "retentionDays": 365
          }
        },
        
        {
          "name": "VulnerabilityAssessment",
          "type": "vulnerabilityAssessments",
          "apiVersion": "2018-06-01-preview",
          "dependsOn": [
            "[concat('Microsoft.Sql/servers/', parameters('serverName'))]",
            "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/auditingSettings/Default')]",
            "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/securityAlertPolicies/DefaultSAP')]"
          ],
          "properties": {
            "storageContainerPath": "[concat('https://',parameters('storageAccountName'),'.blob.core.windows.net','/vulnerability-assessment')]",
            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
            "recurringScans": {
                "isEnabled": true,
                "emailSubscriptionAdmins": true,
                "emails": []
            }
          }
        }
      ]
    },
    {
      "type": "Microsoft.Network/privateEndpoints",
      "apiVersion": "2020-06-01",
      "name": "[parameters('privateEndpointName')]",
      "location": "[parameters('location')]",
      "dependsOn": [
         "[parameters('serverName')]"
      ],
      "properties": {
        "subnet": {
          "id": "[resourceId(parameters('vnetRg'), 'Microsoft.Network/virtualNetworks/subnets', parameters('vnetName'), parameters('subnet1Name'))]"
        },
        "privateLinkServiceConnections": [
          {
            "name": "[parameters('privateEndpointName')]",
            "properties": {
              "privateLinkServiceId": "[resourceId('Microsoft.Sql/servers',parameters('serverName'))]",
              "groupIds": [
                "sqlServer"
              ]
            }
          }
        ]
      },
      "tags": {
        "BUSINESS-OWNER": "[parameters('BUSINESS-OWNER')]",
        "COST-CENTER": "[parameters('COST-CENTER')]",
        "LIFECYCLE": "[parameters('LIFECYCLE')]",
        "APPLICATION": "[parameters('APPLICATION')]",
        "PROJECT-CODE": "[parameters('PROJECT-CODE')]",
        "TECHNICAL-OWNER": "[parameters('TECHNICAL-OWNER')]",
        "GL-CODE": "[parameters('GL-CODE')]"
      }
    }
  ],
  "outputs": {
  }
}

Answer 1

解决:我的ARM模板格式有问题，但最重要的是，在EASTUS中提供SQL Server时，用于创建Pvt端点的VNet区域被设置为EASTUS2。在修复区域和ARM模板之后，我能够成功地部署它。以下是修正后的ARM模板：

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters":{

     "serverName": {
      "type": "string",
      "metadata": {
        "description": "The name of the new database server to create."
      }
    },
    "location": {
      "type": "string",
      "metadata": {
        "description": "The location of the database server."
      }
    },
    "serverVersion": {
      "type": "string",
      "defaultValue" : "12.0"
    },
    "administratorLogin": {
      "type": "string",
      "metadata": {
        "description": "The account name to use for the database server administrator."
      }
    },
    "administratorLoginPassword": {
      "type": "securestring",
      "metadata": {
        "description": "The password to use for the database server administrator."
      }
    },
    "storageAccountName": {
      "type": "string",
      "metadata": {
        "description": "The name of the new storage account to create."
      }
    },
    "emailAddresses": {
      "type": "string",
      "defaultValue": "",
      "metadata": {
        "description": "Email address for alerts."
      }
    },
    "privateEndpointName": {
      "type": "string"
    },
    "vnetName": {
      "type": "string"
    },
    "vnetRg": {
          "type": "string",
          "metadata": {
              "description": "Resource Group Name of VNet"
          }
    },
    "subnet1Name": {
      "type": "string"
    },
    "storageType": {
      "type": "string",
      "defaultValue": "Standard_GRS",
      "allowedValues": [
        "Standard_LRS",
        "Standard_ZRS",
        "Standard_GRS",
        "Standard_RAGRS",
        "Premium_LRS"
      ]
    },
    "BUSINESS-OWNER": {
        "type": "string"
    },
    "COST-CENTER": {
        "type": "int"
    },
    "LIFECYCLE": {
        "type": "string"
    },
    "APPLICATION": {
      "type": "string"
    },
    "PROJECT-CODE": {
      "type": "string"
    },
    "TECHNICAL-OWNER": {
      "type": "string"
    },
    "GL-CODE": {
      "type": "string"
    }   
  },
    "variables": {
        "databaseName": "[concat(parameters('serverName'),'/sample-db')]"
        
    },
    "resources": [
        {
            "type": "Microsoft.Sql/servers",
            "apiVersion": "2020-02-02-preview",
            "name": "[parameters('serverName')]",
            "location": "[parameters('location')]",
            "tags": {
                "displayName": "[parameters('serverName')]",
                "BUSINESS-OWNER": "xxx",
                "COST-CENTER": "11",
                "LIFECYCLE": "xx",
                "APPLICATION": "xx",
                "PROJECT-CODE": "xx",
                "TECHNICAL-OWNER": "xxx",
                "GL-CODE": "111"
            },
            "kind": "v12.0",
            "properties": {
                "administratorLogin": "[parameters('administratorLogin')]",
                "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
                "version": "12.0",
                "minimalTlsVersion": "1.2",
                "publicNetworkAccess": "Disabled"
            },
            "resources": [
                {
            "type": "Microsoft.Sql/servers/databases",
            "apiVersion": "2020-02-02-preview",
            "name": "[variables('databaseName')]",
            "location": "[parameters('location')]",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
            ],
            "tags": {
                "displayName": "[variables('databaseName')]",
                "BUSINESS-OWNER": "xxx",
                "COST-CENTER": "11",
                "LIFECYCLE": "xx",
                "APPLICATION": "xx",
                "PROJECT-CODE": "xx",
                "TECHNICAL-OWNER": "xxx",
                "GL-CODE": "111"
            },
            "sku": {
                "name": "Basic",
                "tier": "Basic",
                "capacity": 5
            },
            "properties": {
                "collation": "SQL_Latin1_General_CP1_CI_AS",
                "edition": "Basic",
                "maxSizeBytes": 104857600,
                "requestedServiceObjectiveName": "Basic",
                "sampleName": "AdventureWorksLT"
            }
           
        },
        {
            "type": "Microsoft.Network/privateEndpoints",
            "apiVersion": "2020-06-01",
            "name": "[parameters('privateEndpointName')]",
            "location": "[parameters('location')]",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
            ],
            "tags": {
                "BUSINESS-OWNER": "xxx",
                "COST-CENTER": "11",
                "LIFECYCLE": "xx",
                "APPLICATION": "xx",
                "PROJECT-CODE": "xx",
                "TECHNICAL-OWNER": "xxx",
                "GL-CODE": "111"
            },
            "properties": {
                "subnet": {
                    "id": "[resourceId(parameters('vnetRg'), 'Microsoft.Network/virtualNetworks/subnets', parameters('vnetName'), parameters('subnet1Name'))]"
                },
                "privateLinkServiceConnections": [
                    {
                        "name": "[parameters('privateEndpointName')]",
                        "properties": {
                            "privateLinkServiceId": "[resourceId('Microsoft.Sql/servers',parameters('serverName'))]",
                            "groupIds": [
                                "sqlServer"
                            ]
                        }
                    }
                ]
            }
        },
        {
          "name": "Default",
          "type": "auditingSettings",
          "apiVersion": "2017-03-01-preview",
          "location": "[parameters('location')]",
          "dependsOn": ["[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"],
          "properties": {
            "State": "Enabled",
            "storageEndpoint": "[concat('https://',parameters('storageAccountName'),'.blob.core.windows.net')]",
            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
            "retentionDays": 365,
            "auditActionsAndGroups": null,
            "storageAccountSubscriptionId": "[subscription().subscriptionId]",
            "isStorageSecondaryKeyInUse": false,
            "isAzureMonitorTargetEnabled": false
          }
        },
        {
          "name": "DefaultSAP",
          "type": "securityAlertPolicies",
          "apiVersion": "2017-03-01-preview",
          "dependsOn": ["[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
            "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/auditingSettings/Default')]"
          ],
          "properties": {
            "state": "Enabled",
            "disabledAlerts": null,
            "emailAddresses": "[array(parameters('emailAddresses'))]",
            "emailAccountAdmins": true,
            "storageEndpoint": "[concat('https://',parameters('storageAccountName'),'.blob.core.windows.net')]",
            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
            "retentionDays": 365
          }
        }

            ]
        }
        
        
        
    ]
}

Answer 2

请确保您在模板上使用的子网已分配Microsoft.Sql类型名称，这意味着它已经是虚拟服务终结点。

以下脚本可以将类型名称Microsoft.Sql添加到您的子网。但是，只有当您的子网缺少类型名称时，脚本才会尝试添加。

### 1. LOG into to your Azure account, needed only once per PS session.  Assign variables.
$yesno = Read-Host 'Do you need to log into Azure (only one time per powershell.exe session)?  [yes/no]'
if ('yes' -eq $yesno) { Connect-AzAccount }

# Assignments to variables used by the later scripts.
# You can EDIT these values, if necessary.

$SubscriptionName = 'yourSubscriptionName'
Select-AzSubscription -SubscriptionName "$SubscriptionName"

$ResourceGroupName = 'yourRGName'
$VNetName = 'yourVNetName'
$SubnetName = 'yourSubnetName'
$SubnetAddressPrefix = 'Obtain this value from the Azure portal.' # Looks roughly like: '10.0.0.0/24'

$ServiceEndpointTypeName_SqlDb = 'Microsoft.Sql'  # Do NOT edit. Is official value.

### 2. Search for your virtual network, and then for your subnet.
# Search for the virtual network.
$vnet = $null
$vnet = Get-AzVirtualNetwork -ResourceGroupName $ResourceGroupName -Name $VNetName

if ($vnet -eq $null) {
    Write-Host "Caution: No virtual network found by the name '$VNetName'."
    return
}

$subnet = $null
for ($nn = 0; $nn -lt $vnet.Subnets.Count; $nn++) {
    $subnet = $vnet.Subnets[$nn]
    if ($subnet.Name -eq $SubnetName) { break }
    $subnet = $null
}

if ($null -eq $subnet) {
    Write-Host "Caution: No subnet found by the name '$SubnetName'"
    Return
}

### 3. Is your subnet tagged as 'Microsoft.Sql' endpoint server type?
$endpointMsSql = $null
for ($nn = 0; $nn -lt $subnet.ServiceEndpoints.Count; $nn++) {
    $endpointMsSql = $subnet.ServiceEndpoints[$nn]
    if ($endpointMsSql.Service -eq $ServiceEndpointTypeName_SqlDb) {
        $endpointMsSql
        break
    }
    $endpointMsSql = $null
}

if ($null -eq $endpointMsSql) {
    Write-Host "Good: Subnet found, and is already tagged as an endpoint of type '$ServiceEndpointTypeName_SqlDb'."
    return
} else {
    Write-Host "Caution: Subnet found, but not yet tagged as an endpoint of type '$ServiceEndpointTypeName_SqlDb'."

    # Ask the user for confirmation.
    $yesno = Read-Host 'Do you want the PS script to apply the endpoint type name to your subnet?  [yes/no]'
    if ('no' -eq $yesno) { return }
}

### 4. Add a Virtual Service endpoint of type name 'Microsoft.Sql', on your subnet.
$setParams = @{
    Name            = $SubnetName
    AddressPrefix   = $SubnetAddressPrefix
    VirtualNetwork  = $vnet
    ServiceEndpoint = $ServiceEndpointTypeName_SqlDb
}
$vnet = Set-AzVirtualNetworkSubnetConfig @setParams

# Persist the subnet update.
$vnet = Set-AzVirtualNetwork -VirtualNetwork $vnet

for ($nn = 0; $nn -lt $vnet.Subnets.Count; $nn++) {
    $vnet.Subnets[0].ServiceEndpoints # Display.
}