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 myexternalip.com, 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
[array]$AzureServers = @('ServerName1','ServerName2','ServerName3'<# etc, etc as needed#>);
$wc = new-object System.Net.WebClient
$ip = $wc.DownloadString("http://myexternalip.com/raw").ToString().Trim();
[string]$RuleName = 'MyDefaultRuleName',
$CurrentRule = Get-AzureSqlDatabaseServerFirewallRule -RuleName $RuleName -ServerName $ServerName;
$CurrentIp = $CurrentRule.StartIpAddress
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 "My 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.