Programmatically setting Azure SQL Database firewall rules

During my talk “Azure SQL DB: 12 Things to Know ,” I briefly discuss the importance of monitoring and updating the IP addresses allowed to connect to your Azure SQL DB. Efficiently managing those firewall rules means you want to script them. In this post, I will provide a few ideas of how you can manage the database server rules using PowerShell scripts.

If you need to log on to Azure with PowerShell, read this post.

PowerShell Commandlets

The PowerShell cmdlets to use are

• New-AzureRmSqlServerFirewallRule to create a new firewall rule
• Get-AzureRmSqlServerFirewallRule to retrieve existing firewall rules

Each of these cmdlets need parameters of course, and I’ll demonstrate those as I show each case.

Retrieving Existing Rules

There is a limit on the number of rules you can add to an Azure SQL DB server firewall. You also don’t want to have to review tons of duplicate rules. So before adding any rule, I will get the list of existing rules. I can then run a Where filter to determine if a particular IP has already been added.

The cmdlet is Get-AzureRmSqlServerFirewallRule and you need to provide the -ServerName and -ResourceGroupName parameters. I define the server name and resource group name as script variables because they are needed for many cmdlets.

$RgName = 'Your Resource Group Name'
$SrvName = 'Your SQL Server Name'
$currentRules = Get-AzureRmSqlServerFirewallRule `
    -ServerName $SrvName `
    -ResourceGroupName $RgName

Static IPs

There are probably some addresses that you’ll always want to allow access from, such as the outbound IPs of your data center. You can list those in your script using a string array.

$staticIPs = "1.2.3.4", "1.2.3.5"

List in hand, you can loop through the array and check for the existence of each rule:

Foreach ($ip in $staticIPs)
{
    $rule = $currentRules | Where ($_.StartIpAddress -eq $ip)
    # If a rule with this IP does not exist
    If (!$rule)
    {
        Write-Host No rule for $ip - creating
        New-AzureRmSqlServerFirewallRule -ResourceGroupName $RgName `
        -ServerName $SrvName -StartIpAddress $ip `
        -EndIpAddress $ip -FirewallRuleName $ip
    }
}

You’ll note I am only comparing StartIpAddress. We could use IP address ranges and the concepts would be very similar. If you have an interest in seeing a script that uses ranges, please leave some feedback.

I am also using the IP address as the name of the rule. You could define custom names, for example by having a second string array that defines the rule name for each IP address. I am using that technique in the full script you can download below.

The output of the command is shown below.

Current (Dynamic) IP

It’s also practical to have a way to add your current IP address. This comes in handy when you are working from a location where the external IP address is dynamic. In order to find your current external IP, you can make a call to a web service that will return that to you. There are many such services available; currently, I use ipify (https://api.ipify.org?format=json), compliments of Randall Degges.

$ThisIp = (Invoke-RestMethod https://api.ipify.org?format=json).ip
New-AzureRmSqlServerFirewallRule -ResourceGroupName $RgName `​
    -ServerName $SrvName `
    -StartIpAddress $ThisIp -EndIpAddress $ThisIp `
    -FirewallRuleName "Current IP"

Setting Rules for Azure Web Apps

Early on, I set my Azure SQL DB firewall to allow connections from all Azure services. I stopped doing that however when I realized that anyone trying to get access to my data would just need to set up any Azure service, which is easy and cheap, to bypass the firewall. The database server firewall is not the only defense I have, but it’s an important part. I strongly discourage you from allowing access to your Azure SQL DBs from all Azure IPs.

When you create a new server from the GUI, you are unable to uncheck that setting. Make it the first thing you do when the server is created!

Unless you use a Premium tier feature, your Azure Web Apps have 4 outbound IP addresses. App Service Environments support a static IP. The idea here is to retrieve these IPs with PowerShell. The IP address will be available as a single string with each IP separated by commas. I will create an array and then use the same code to check if rules exist for each IP and create a rule if it doesn’t.

Your Azure Web App IPs won’t change often, if at all. They are likely to change if you make a change to the scale set or location of your web app. If you want to guard against extended application downtime from such changes, you could run this script on a schedule using Azure Functions.

$WebAppName = 'Your Web App Name'
$webAppIPs = (Get-AzureRmResource -ResourceGroupName $RgName -ResourceType Microsoft.Web/sites -ResourceName $webAppName).Properties.outboundIpAddresses
$webAppIPs = $webAppIPs.Split(',')

# Loop through all 4 IP addresses of the web app
Foreach ($ip in $webAppIPs)
{
    $rule = $currentRules | Where ($_.StartIpAddress -eq $ip)

    # If a rule with this IP does not exist
    If (!$rule)
    {
        Write-Host No rule for $ip - creating
        New-AzureRmSqlServerFirewallRule -ResourceGroupName $RgName `
            -ServerName $SrvName -StartIpAddress $ip `
            -EndIpAddress $ip -FirewallRuleName "Web App IP $i"
    }

    $i++
}

Note: you can use the same approach for other Azure services that need access to your DB. You can obtain the outbound IPs of any Azure service – check the commandlets for your service.

I have posted a complete version of the script on GitHub:

Removing Unwanted Rules

After going through all these steps, you have ensured that all IPs that you want to have access to your server have access. However, there may be orphaned rules; rules that were added previously but are now no longer wanted.

My way to solve this technique is to track all wanted IPs in an array. I do this by merging the static IP array, my current IP, and the IPs from web app(s) into a single array. I then loop through the existing firewall rules and attempt to find each rule’s IP address in the IP address array. If it doesn’t exist, the rule is removed.