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.

TL;DR

• Get your current public IP in PowerShell: (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content.
• Create a rule: az sql server firewall-rule create --name "..." --resource-group "..." --server "..." --start-ip-address X --end-ip-address Y.
• Update an existing rule with az sql server firewall-rule update, using the same --name as the identifier.
• List with az sql server firewall-rule list, inspect one with az sql server firewall-rule show.
• For a dynamic public IP, automate create-or-update across all SQL servers in a subscription using az sql server list --query.

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

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

https://gist.github.com/mhdbouk/21a9d18a67336a4e12e6f99ad73fbdcc

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

FAQ

How do I add my public IP to an Azure SQL Server firewall?

Run az sql server firewall-rule create --name "<rule-name>" --resource-group "<rg>" --server "<sql-server>" --start-ip-address <ip> --end-ip-address <ip>. For a single address, set start and end to the same IP. The rule appears immediately in the Networking blade of the SQL Server in the Azure Portal.

How do I update an existing Azure SQL firewall rule?

Use az sql server firewall-rule update with the same --name. The name is the identifier, so passing new --start-ip-address and --end-ip-address values overwrites the existing rule in place. If the rule does not exist, the command fails; use create instead.

How can I list all Azure SQL firewall rules with the CLI?

az sql server firewall-rule list --resource-group "<rg>" --server "<sql-server>" returns a JSON array of all rules. Use --query "[].name" to get just rule names, or --query "[?contains(name, 'home')]" to filter.

How do I automatically update Azure SQL firewall rules when my public IP changes?

Schedule a PowerShell script that fetches your current public IP, iterates over each SQL server in your subscription using az sql server list --query "[].{name:name, rg:resourceGroup}", and runs firewall-rule create (or update if the rule already exists). Run it on a timer or trigger it manually whenever your ISP rotates your IP.

What’s the difference between Azure SQL firewall rules and virtual network rules?

Server-level firewall rules allow specific IPv4 ranges to reach the SQL endpoint over the public internet. Virtual network rules tie access to a subnet inside an Azure VNet, with no public exposure. Use firewall rules for developer laptops on a corporate network. Use VNet rules (or Private Endpoints) for production workloads that should never traverse the public internet.

How do I get my current public IP in PowerShell?

(Invoke-WebRequest -uri "http://ifconfig.me/ip").Content returns your public IPv4 as a string. Alternatives: (Invoke-RestMethod "https://api.ipify.org") or curl -s ifconfig.me on Linux/macOS. Store the result in a variable and pass it to the az command.