We are in the process of creating SQL Server using ARM Template but since there are policies in place we are unable to get SQL Server created following are the 2 policy exceptions:
##[error]Resource 'xxxxx' was disallowed by policy. Error Type: PolicyViolation, Policy Definition Name : SQL Server should use a virtual network service endpoint (NPD), Policy Assignment Name : NPD1a8a9dc8-aef3-421a-93. Error Type: PolicyViolation, Policy Definition Name : Auditing on SQL server should be enabled (NPD), Policy Assignment Name : NPD7885d0ef-a3de-44a3-9a.
Following is the ARM Template we are using and I am not sure why its failing as now we have VNet rules and auditing also enabled as part of the SQL Server creation:
{
"$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": {
}
}
