Recently on Microsoft Q&A there was question on how you can control Azure SQL Firewall rule in a way that only certain IP addresses are allowed to be configured. Naturally I gave general answer that you can do that via Azure Policy. Initially I didn’t give the person an actual policy as I haven’t done such before. Of course creating Azure Policy definition can be challenging so the person asked him if I can provide him with example.
Needless to say a few hours later I was ready with the following policy rule definition:
{
"mode": "All",
"policyRule": {
"if": {
"allOf": [
{
"field": "Microsoft.Sql/servers/firewallRules/startIpAddress",
"notIn": "[parameters('listOfStartIpAddresses')]"
},
{
"field": "Microsoft.Sql/servers/firewallRules/endIpAddress",
"notIn": "[parameters('listOfEndIpAddresses')]"
}
]
},
"then": {
"effect": "[parameters('effect')]"
}
},
"parameters": {
"effect": {
"type": "String",
"metadata": {
"displayName": "Effect",
"description": "Enable or disable the execution of the policy"
},
"allowedValues": [
"Audit",
"Deny",
"Disabled"
],
"defaultValue": "Deny"
},
"listOfStartIpAddresses": {
"type": "Array",
"metadata": {
"displayName": "List of Start IP Addresses for SQL",
"description": "List of Start IP Addresses for SQL"
}
},
"listOfEndIpAddresses": {
"type": "Array",
"metadata": {
"displayName": "List of End IP Addresses for SQL",
"description": "List of End IP Addresses for SQL"
}
}
}
}
A few things to notice:
- I was able to find the resource aliases via Get-AzPolicyAlias -ResourceTypeMatch ‘servers/firewallRules’ -NamespaceMatch ‘Microsoft.Sql’
- mode is set to All as this resource does not support tags or location
- The effect can be chosen via parameter
- In case you need to allow specific IP address rather range the IP address needs to be in both list of addresses
It is important to note that a policy to audit if specific rule exists is available on GitHub. That policy will only monitor if the rule for specific range exists but you cannot enforce it like the example above as it has auditIfNotExists effect.
Cloud Administrator in Azure World 
This policy does not work with deny effect because of the known issues described here for this resource type https://github.com/Azure/azure-policy#nonstandard-creation-pattern
Hello. Thx for great article. Unfortunately it does not work. I’ve created definition and then assignment. But i can still can create 0.0.0.0-255.255.255.255 rule in sql fw
{
“mode”: “All”,
“parameters”: {},
“policyRule”: {
“if”: {
“anyOf”: [
{
“field”: “Microsoft.Sql/servers/firewallRules/startIpAddress”,
“equals”: “0.0.0.0”
},
{
“field”: “Microsoft.Sql/servers/firewallRules/endIpAddress”,
“equals”: “0.0.0.0”
}
]
},
“then”: {
“effect”: “Deny”
}
}
}