You've successfully subscribed to Jeremy Foster
Great! Next, complete checkout for full access to Jeremy Foster
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.
Azure SQL Firewall Rule

Azure SQL Firewall Rule

. 2 min read

This post is about adding an IP rule to an Azure SQL Server Firewall granting access to an external user. Mostly it's about me playing around with source code in markdown but, here goes.

Problem:
(Very) often, developers, analysts, engineers and data-scientists will require access to an Azure SQL Server and the most common way to provision that access is to use the IP-based firewall. Now if you've ever found yourself doing a job over and over again, one of the ways to cure that feeling is to automate the task. This is one way of doing so using a bash script and a csv file.

The Files:
We'll need a csv (comma-separated-value) file that opens nicely in apps like Excel or numbers and we'll need a bash script. The csv file contains the values required for the bash script to execute the task of adding the firewall rule to the SQL server. Simple right?

# CSV file format
# az sql firewall rule function takes these required args
# subscription,resourceGroup,serverName,ruleName,ipAddress

my-subscription,my-resGroup,sqlServer1,Office-Jim-Beam,54.22.33.65
another-sub,new-resGroup,sqlServer2,Home-Jane-Doe,20.36.59.81
prod-sub,prod-resGroup,sqlServer3,Home-Terry-Jones,22.33.55.66
# takes 5 params:
# subscription resourcegroup serverName ruleName ipAddress
# This will create the new IP allow rule on the SQL Firewall
# helps to run `az login` if running locally
createrule() {
   az account set --subscription "$1"
   az sql server firewall-rule create -g $2 \
     -s $3 -n $4 --start-ip-address $5 --end-ip-address $5
 }

INPUT=rules.csv
OLDIFS=$IFS
IFS=','
[ ! -f $INPUT ] && { echo "$INPUT file not found"; exit 99; }
 
# read each row of the csv file and parse out as values
while read subscription resourcegroup servername rulename ipaddress
do
  createrule $subscription $resourcegroup $servername $rulename $ipaddress
done < $INPUT
IFS=$OLDIFS

Now when we run the shell script locally, ./createrule.sh it will parse the csv file and pass the values from each row into the create rule function. I know, easy peasy, but hooked into a pipeline, with a large csv file of new hires, users with IP changes etc... and you're going to look like a winner adding hundreds of firewall rules in an automated way. Happy Coding!