PowerShell script to update Azure firewall rule

*Updated 2017-06-28

I’ve recently moved house, and as a result had to change my broadband plan from cable to ADSL (sad face). This also means I’ve gone from having a fixed IP address to a dynamically assigned one. Usually, this wouldn’t be a problem, except when it comes to connecting to the several Azure servers that I manage on a daily basis. Now I need to use the Azure Portal to manually change each server’s firewall settings at least once or twice a week. Painfull…

So I quickly threw together this PS script to do the job for me and thought others out there might find it useful too.

How’s it work?

The script accepts an array of Azure SQL Server names, finds your external IP address using ipinfo.io, and then loops through the list of servers. You’ll need to provide a default rule name or modify the function call to pass it in (maybe include it in the array if it’s different for each server?).

It then checks the current IP address of the specified rule and, if it’s different to your external IP address, updates the firewall rule for you. #Magic

Import-Module SQLPS -DisableNameChecking 
Import-Module Azure 

# Run Get-AzurePublishSettingsFile first to download the publish settings file for your Azure subscription
# Full instructions here: https://docs.microsoft.com/en-us/powershell/module/azure/get-azurepublishsettingsfile?view=azuresmps-4.0.0

Import-AzurePublishSettingsFile "C:\My_oresome_path\Sweet-as-publish-settings-file.publishsettings" # <-- put the path to your publish settings file here

# Now just add your server names to this array... or get fancy and look them up somehow, 
# whether from a simple text file or something more exotic.
[array]$AzureServers = @('servername01','servername02','servername03','servername04'); 

# Just a little function to get your current external/public IP address
function Get-MyIpAddress
{
    $ip = Invoke-RestMethod http://ipinfo.io/json | Select -exp ip
    return $ip;
}

# This function does the work of changing the whitelist if necessary
function Update-MyAzureFirewallRule 
{
    Param (
        [Parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelinebyPropertyName=$True)]
        [string]$ServerName,
        [string]$RuleName = 'Put_Your_Rule_Name_Here',
        [string]$IpAddress
        )

    # Gets the current rule (so we can see what the IP address is currently set to)
    $CurrentRule = Get-AzureSqlDatabaseServerFirewallRule -RuleName $RuleName -ServerName $ServerName; 
    $CurrentIp = $CurrentRule.StartIpAddress

    # If your current IP doesn't match what's in the whitelist, then update it
    if ($CurrentIp -ne $IpAddress)
    {
        Write-Host "Setting firewall rule '$RuleName' on server '$ServerName' to IP address '$IpAddress' (was '$CurrentIp')..."
        Set-AzureSqlDatabaseServerFirewallRule -StartIPAddress $IpAddress -EndIPAddress $IpAddress -RuleName $RuleName -ServerName $ServerName;
    }
    
}

if ($IpAddress = Get-MyIpAddress)
{
    Write-Host "Your IP address is $IpAddress"

    foreach ($s in $AzureServers) 
    {
        Update-MyAzureFirewallRule -ServerName $s -IpAddress $IpAddress;
    }
}

This post provided the inspiration, which I then tweaked it to suit my needs. Like I said; it’s quick-and-dirty, so use at your own risk. 😉  I’m no PowerShell guru either, so feel free to let me know if you improve on it.

Cheers,
Dave