Hi,
I created two policies to work over the new SQL Feature to enforce AD Authentication on Azure SQL Servers.
One of them, using AuditIfNotExists effect, works fine. The other one, using DeployIfNotExists, doesn't.
I imagine the biggest challenge is to use the correct role permission, considering the DeployIfNotExists effect requires a role permission in order to create the managed identity to execute the deployment.
I chose the SQL Security Manager role, /providers/Microsoft.Authorization/roleDefinitions/056cd41c-7e88-42e1-933e-88ba6a50c9c3 . It was correctly identified on the role assignment.
I can also see the role assignment on the resource group RBAC access control.
However, the deployment requested on the effect never happens. The policy identifies the resource is not compliant, but the deployment never happens to change the property as it should.
I'm not sure what I may be missing and maybe one of the main questions is: How could I track the effect execution to identify what's wrong with this policy?
The policy definition:
{
"properties": {
"displayName": "Deploy SQL Integrated Security",
"policyType": "Custom",
"mode": "All",
"description": "Force all Azure SQL Servers to use Integrated Security only",
"metadata": {
"category": "SQL",
"createdBy": "065ae953-dd11-413c-a4a6-bc1eb6f55fcc",
"createdOn": "2021-06-16T14:49:33.2911598Z",
"updatedBy": "065ae953-dd11-413c-a4a6-bc1eb6f55fcc",
"updatedOn": "2021-06-18T16:23:11.2510009Z"
},
"parameters": {},
"policyRule": {
"if": {
"allOf": [
{
"field": "type",
"equals": "Microsoft.Sql/servers"
}
]
},
"then": {
"effect": "deployIfNotExists",
"details": {
"type": "Microsoft.Sql/servers/azureADOnlyAuthentications",
"roleDefinitionIds": [
"/providers/Microsoft.Authorization/roleDefinitions/056cd41c-7e88-42e1-933e-88ba6a50c9c3"
],
"existenceCondition": {
"allOf": [
{
"field": "Microsoft.Sql/servers/azureADOnlyAuthentications/azureADOnlyAuthentication",
"equals": true
}
]
},
"deployment": {
"properties": {
"mode": "incremental",
"name": "Default",
"template": {
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"fullDbName": {
"type": "string"
}
},
"resources": [
{
"name": "[concat(parameters('fullDbName'), '/Default')]",
"apiVersion": "2021-02-01-preview",
"type": "Microsoft.Sql/servers/azureADOnlyAuthentications",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('fullDbName'))]"
],
"properties": {
"azureADOnlyAuthentication": true
}
}
]
},
"parameters": {
"fullDbName": {
"value": "[field('name')]"
}
}
}
}
}
}
}
},
"id": "/providers/Microsoft.Management/managementGroups/52b6b910-1fc7-44e7-b03d-ffb4ea2dd90b/providers/Microsoft.Authorization/policyDefinitions/9daaafde-73ff-4966-9037-445dc937474d",
"type": "Microsoft.Authorization/policyDefinitions",
"name": "9daaafde-73ff-4966-9037-445dc937474d"
}