Update Azure SQL Server Firewall rules using Azure CLI

If you’re used to working with Azure SQL Server without a private endpoint or virtual network, you might be used to adding your public IP to the Azure SQL Server Firewall rules each time your public IP changes. Luckily, there’s an easy option: use the Azure CLI to update the network firewall rules with your new public IP.

Get the Public IP using PowerShell

Getting your public IP address is quick and easy with PowerShell. Just use the following command:

$ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content

This will invoke an HTTP web request to http://ifconfig.me/ip and store the content of the request in a new variable called $ip (we are going to use that later in the automation script)

Azure CLI

Note #1

Make sure that Azure CLI is installed (Official Documentation) and that you are authenticated using az login command

Create Firewall Rule

To create a new firewall rule using Azure CLI, you can do it using the following command

az sql server firewall-rule create --name "Local Home" --resource-group "rg-sql-customers-prod" --server "sql-customers-prod-001" --start-ip-address 1.2.3.4 --end-ip-address 5.6.7.8

--name specify the name of the firewall rule, this will be shown in the Networking tab in the Azure Portal

--resource-group specify the azure SQL server resource group name

--server specify the azure SQL server name

--start-ip-address the start IPv4 address

--end-ip-address the end IPv4 address

Note #2

If you need to create a range rule you can specify a beginning and end public IP address, but if you have only one public IP to be set, both --start-ip-address and --end-ip-address should be the same

Once you run the create command, you will get the JSON response of the created resource (the rule).

{
  "endIpAddress": "5.6.7.8",
  "id": "/subscriptions/xxx/resourceGroups/rg-sql-customers-prod/providers/Microsoft.Sql/servers/sql-customers-prod-001/firewallRules/Local Home",
  "name": "Local Home",
  "resourceGroup": "rg-sql-customers-prod",
  "startIpAddress": "1.2.3.4",
  "type": "Microsoft.Sql/servers/firewallRules"
}

The same thing can be shown in the Azure portal, the new firewall rule can be located in the Networking tab as shown in the screenshot below

The Rule name, the start IP, and the end IP addresses in Azure Portal

Update an existing Firewall Rule

To update the firewall rule, you can do that using the following

az sql server firewall-rule update --name "Local Home" --resource-group "rg-sql-customers-prod" --server "sql-customers-prod-001" --start-ip-address 1.2.3.4 --end-ip-address 5.6.7.8

Here the only thing being updated is the start-ip-address and the end-ip-address since Azure CLI will use the Name as the identifier to update the rule.

List and Show rules

To list all firewall rules configured for a SQL Server, you can type the following command

az sql server firewall-rule list --resource-group "rg-sql-customers-prod" --server "sql-customers-prod-001"

and to show one rule you can

az sql server firewall-rule show --name "Local Home" --resource-group "rg-sql-customers-prod" --server "sql-customers-prod-001"

Let’s Automate things

If you have a static public IP provided by your ISP (internet service provider), there is no need to automate anything, since your public IP rule will be stored once, and no need to change it. But, having a dynamic public IP that changes more often, makes the automation process a must, especially when you have multiple SQL servers that need a manual update. For that scenario, I create the following script to go through all your SQL servers inside a subscription and create/update the firewall rules with the new public IP

	Param([string] $subscription)

	$subscriptionId;
	switch ($subscription)
	{
	'dev' {$subscriptionId = 'xxx'}
	'stg' {$subscriptionId = 'yyy'}
	'prd' {$subscriptionId = 'zzz'}
	}

	if (!$subscriptionId) {
	write-error 'Subscription not found'
	return;
	} else {
	Write-Host "Subscription used $($subscription) with Id: $($subscriptionId)"
	}

	$ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content

	write-host "Public Ip: $($ip)"

	$list = az sql server list –query "[].{name:name, rg:resourceGroup}" –subscription $subscriptionId \| convertfrom-json
	foreach($server in $list) {
	$exists = az sql server firewall-rule list -g $server.rg -s $server.name –subscription $subscriptionId –query "[?name == 'Mohamad Dbouk']" \| convertfrom-json
	if ($exists.length -gt 0) {
	Write-Host "Updated – $($server.name)"
	$result = az sql server firewall-rule update -g $server.rg -s $server.name -n "Mohamad Dbouk" –start-ip-address $ip –end-ip-address $ip –subscription $subscriptionId
	} else {
	Write-Host "Created – $($server.name)"
	$result = az sql server firewall-rule create -g $server.rg -s $server.name -n "Mohamad Dbouk" –start-ip-address $ip –end-ip-address $ip –subscription $subscriptionId
	}
	}

view raw update-sqlserver-firewall-rule.ps1 hosted with ❤ by GitHub

In that script, I’m assuming that we have 3 different subscriptions dev, staging, and production and that we have multiple SQL servers. Note that to get all SQL servers I’m using az SQL server list with the --query parameter to update the JSON response with only the name and resource group. Also, I’m filtering the list of firewall rules to check the existing rule using the --query as well.
Happy coding and automation

2 thoughts on “Update Azure SQL Server Firewall rules using Azure CLI”

mallena January 4, 2023 at 7:10 am

informative and useful tooo!! thanks for sharing.

Reply ↓
1. Mohamad Dbouk Post authorJanuary 4, 2023 at 8:46 am
  
  I’m glad you found it helpful and informative. Thanks for reading and catch you in the next one 😊
  
  Reply ↓