I wrote before about enforcing Azure SQL AD Authentication using Azure Policies in two different ways. First, only auditing if the Azure SQL is using AD Authentication or not. Later, automatically enabling the AD Authentication on Azure SQL Servers where this authentication is not enabled.
What about if we could create one single policy definition and let each IT department in your company decides if the policy will only audit the non-compliance SQL or if they would like to fix them automatically ?
We can parameterize our policy and the policy effect, leaving the choice to the moment of the assignment.
The first thing we need to do is to carefully plan the effects we would like the policy to accept. Audit and Deny are two effects easy to combine, because they don’t have additional parameters. DeployIfNotExists, on the other hand, uses additional parameters, such as existanceCondition. Combining the DeployIfNotExists with the Audit may have bad results, failing the evaluation in one or the other case. Luckily, we have the AuditIfNotExists. We can combine DeployIfNotExists with AuditIfNotExists to get a good result.
The new policy with the effect parameter definition appers like this:
{
"parameters":{
"effect": {
"type": "String",
"metadata": {
"displayName": "Effect",
"description": "Enable or disable the execution of the policy"
},
"allowedValues": [
"DeployIfNotExists",
"AuditIfNotExists",
"Disabled"
],
"defaultValue": "DeployIfNotExists"
}
},
"policyRule":{
"if":{
"allOf":[
{
"field":"type",
"equals":"Microsoft.Sql/servers"
}
]
},
"then":{
"effect": "[parameters('effect')]",
"details":{
"type":"Microsoft.Sql/servers/azureADOnlyAuthentications",
"roleDefinitionIds":[
"/providers/Microsoft.Authorization/roleDefinitions/056cd41c-7e88-42e1-933e-88ba6a50c9c3",
"/providers/Microsoft.Authorization/roleDefinitions/8e3af657-a8ff-443c-a75c-2fe8c4bcb635"
],
"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":{
"fullServerName":{
"type":"string"
}
},
"resources":[
{
"name":"[concat(parameters('fullServerName'), '/Default')]",
"apiVersion":"2021-02-01-preview",
"type":"Microsoft.Sql/servers/azureADOnlyAuthentications",
"properties":{
"azureADOnlyAuthentication":true
}
}
]
},
"parameters":{
"fullServerName":{
"value":"[field('name')]"
}
}
}
}
}
}
}
}
Policy Assignment
The difference will happen when assigning the policy. We will be able to choose which effect we would like to use, AuditIfNotExists or DeployIfNotExists.
In a world wide company, or a company spread across multiple branches, the cloud environment can be organized in multiple management groups and subscriptions. For each Management Group and Subscription the company may have an IT team responsible for it, taking the decisions about how to manage that specific set of projects and cloud services.
This would be similar to my own personal organization you can see on the image below:
That’s the benefit of a parameterized policy: Each IT team can make their own decision about how to manage their area of cloud services.
Load comments