Automating Azure SQL firewall rules based on Azure Service Tags

Azure SQL firewall rules are based on start and end IP addresses and do not use either CIDR notation or have any capability to use Azure Service Tags (like NSGs or Azure firewall can). So if your task is to allow inbound connectivity to Azure SQL only from certain Azure serviceTags then it will require you to manually check IP ranges for specific service tags periodically and manually input/update/delete in Azure SQL firewall rules.

Solution below will assist in this task by periodically pulling JSON file with IP ranges for various service tags in Azure and apply/update them properly in Azure SQL firewall rules. Please note that as of right now (August 2020) ServiceTag API is not returning current information and solution relies on downloading complete JSON file from Azure by parsing HTML contents to find location of latest file.

Deployment steps

ARM template and parameter file can be downloaded from Github repo. Template creates 2 resources (Logic App and API connector) and requires 3 parameters to work.

"LogicAppResourceGroupID": {
 "value": "/subscriptions/8211cd03-4f97-4ee6-af42-38cad1387992/resourceGroups/LogicApp-RG"
"SQLResourceID": {
 "value": "/subscriptions/8211cd03-4f97-4ee6-af42-38cad1387992/resourceGroups/SQL-RG/providers/Microsoft.Sql/servers/whateverserver21"
"ServiceTagName": {
 "value": "HDInsight.EastUS"
  • LogicAppResourceGroupID parameter is Azure Resource ID for ResourceGroup where your logic app is located
  • SQLResourceID parameter is Azure Resource ID for SQL server where you want to modify firewall rules
  • ServiceTagName parameter is name of service tag which you want to incorporate into Azure SQL firewall

Deploy ARM template along with parameter file either from command line or Azure portal.

Once deployed additional step needs to be taken to authorize API connection for LogicApp to perform actions on your behalf. Go to Logic App designer and any step which requires call to Azure API (for example Connections under Try read current Tags. It will have exclamation point next to it. Click on Invalid button and authorize connection on next screen

Run Logic App and if everything works ok you will see following

  • Network Security Group called dummy created in Logic App resource group which will have tag with a name of ServiceTag and Iteration Number. Iteration number identifies latest version of IP ranges for specific ServiceTag.
  • SQL firewall rules update with IP ranges for desired service tag

Implementation details

Logic app specified in ARM template executes following steps

  1. Try to read current tags from network security group and find current Iteration number
  2. Downloads HTML file of the page which allows to download JSON file
  3. Parses HTML file to find location of JSON file
  4. Downloads JSON files
  5. Parses JSON and filters only ServiceTag requested
  6. Compares Iteration Number in downloaded file to current iteration number obtained in step 1
  7. If Iteration number is different then read all current SQL firewall rules
  8. Iterates through each IP range obtained in step above and checks if there is already existing firewall rule corresponding to current item
  9. If there is none then new rule needs to be created
    • API is called to convert CIDR notation into start/end IP addresses
    • Response is parsed
    • New Firewall rule is created
  10. Iterates through all existing firewall rules to find any existing copies of firewall rules which are no longer needed (IP ranges were deleted from servicetag)
  11. If there is a firewall rule which do not have a match with new IP range then firewall rule is deleted
  12. If all steps above succeeded then update NSG tag to latest Iteration number for specific ServiceTag

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s