Today’s post is not about SQL Server, but if you’re working in Azure from a dynamic IP address, you might still find it useful.

Recently, my home ISP has started changing my public IP address. This causes me some headache because I have a couple of Azure Network Security Group rules (think of them as firewall rules) that specifically allow my home IP access to all of my Azure resources. When my home IP changes, those rules have to be updated accordingly.

So I made a PowerShell-based solution to automatically maintain them.

The setup

I don’t really have a proper “server” at home. Though I have some lab environments, I don’t want to rely on those machines and VMs to always be up and running. However, I do have a Raspberry Pi (behind a firewall) running my Flightradar24 receiver, and I’ve previously installed PowerShell Core on that Pi.

The idea is to have the Pi check what my home firewall’s public IP is, then save that public IP to an Azure storage blob, after which an automation job can periodically check that storage blob and update my Network Security Group inbound rules with that IP address.

You could probably run everything on the Pi directly, if you can figure out the Azure authentication mechanisms. I just found the authentication bits easier with a managed identity in Azure Automation.

Setting up the Pi script

The following will work on any computer that runs PowerShell Core. The script calls a web-based API to check what my current external IP is:

Import-Module Az.Storage

(Invoke-WebRequest -Uri "https://fn.strd.co/api/ip" -Method GET).Content | Out-File "~/jobs/ip.txt" -Force

$StorageAccountName = "storageaccountgoeshere" 
$StorageAccountKey = "00000000000000000000000000000000000000000000000000000000000000000000000000000000000000=="
$ContainerName = "containername"

$cx = New-AzStorageContext `
    -StorageAccountName  $StorageAccountName `
    -StorageAccountKey  $StorageAccountKey

Set-AzStorageBlobContent `
    -Context $cx `
    -Container $ContainerName `
    -Blob "ip.txt" `
    -File "~/jobs/ip.txt" `
    -Force

I’m using my own API to figure out my public IP address, but you could change the URL to use any service, like api.myip.com or api.ipify.org, to name a few. This is not an endorsement of any specific service – use your own judgement and read the security notes below. Feel free to use mine (the one in the script) if you want.

Scheduling the Pi script with cron

Since the Pi runs a Linux derivative, the way to schedule jobs is called “cron”. To edit the schedule, run the following command in the prompt, which will open up your preferred editor, like nano.

crontab -e

In the editor, you can add and remove jobs. I’ve chosen to run my script every 30 minutes, using the following syntax:

0,30 * * * * /usr/bin/pwsh ~/jobs/update-public-ip.ps1 >/dev/null

Here’s a description of the cron scheduling syntax.

My own IP service

If you decide to set up your on API, here’s the Azure Function Javscript code I’ve been using:

module.exports = async function (context, req) {
    context.log('JavaScript HTTP trigger function processed a request.');

    client = req.headers['x-forwarded-for']

    if (client.substr(0, 1) == '[') {
        // IPv6
        client = client.match(/\[(.*)\]/)[0].replace('[', '').replace(']', '');
    } else {
        // IPv4
        client = client.split(':')[0];
    }

    context.res = {
        // status: 200, /* Defaults to 200 */
        body: client
    };
};

Azure Automation job

Here’s the code for my scheduled PowerShell job that runs in Azure Automation. It updates Network Security Group rules as well as the firewall rules for Azure SQL Database instances. Don’t just copy-paste this code – review and adapt it to your specific needs.

$ErrorActionPreference = "Stop"

$subscriptionId = "xxxxxxx"
$ResourceGroup = "yyyyyyyyy"
$StorageAccountName = "storageaccount" 
$ContainerName = "firewall"
$ruleName = "HomeSweetHome"

# Authenticate
# ------------

try {
	Connect-AzAccount -Identity
	Set-AzContext -SubscriptionId $subscriptionId

    Write-Output -Message "Connected."
} catch {
    throw ('Error occurred while creating connection: {0}' -f $_);
}

# Get the IP from the storage blob
# --------------------------------

$cx = New-AzStorageContext -StorageAccountName $StorageAccountName
Get-AzStorageBlobContent -Container $ContainerName -Context $cx -Blob "ip.txt" -Force
$publicIp = Get-Content "ip.txt"

# Network security groups
# -----------------------

$NSGs = Get-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroup

foreach ($NSG in $NSGs) {
    ($NSG.SecurityRules | Where-Object {$_.Name -eq $ruleName}).SourceAddressPrefix = ([System.String[]] @($publicIp))
    $NSG | Set-AzNetworkSecurityGroup | Get-AzNetworkSecurityRuleConfig -Name $ruleName
}

# Azure SQL Servers
# -----------------

$SqlServers = Get-AzSqlServer -ResourceGroupName $ResourceGroup

foreach ($SQLServer in $SQLServers) {
    $rule = Get-AzSqlServerFirewallRule `
        -ResourceGroupName $ResourceGroup `
        -ServerName $SQLServer.ServerName `
        -FirewallRuleName $ruleName
    
    if ($rule) {
        Set-AzSqlServerFirewallRule `
            -ResourceGroupName $ResourceGroup `
            -ServerName $SQLServer.ServerName `
            -FirewallRuleName $rule.FirewallRuleName `
            -StartIpAddress $publicIp `
            -EndIpAddress $publicIp
    }
}

Security

There are some important things you need to keep track of with this type of solution.

• Anyone who controls the external IP API could theoretically insert any IP into your NSG rules. It’s far-fetched, but not impossible.
• The Azure Automation managed identity needs to be able to read the storage account.
• Anyone who can write files to the blob storage container, can insert an IP into your NSG rules.