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
Import-Module Azure
cls
[array]$AzureServers = @(‘ServerName1’,‘ServerName2’,‘ServerName3’<# etc, etc as needed#>);
function Get-MyIpAddress
{
$wc = new-object System.Net.WebClient
return $ip;
}
function Update-MyAzureFirewallRule
{
Param (
[Parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelinebyPropertyName=$True)]
[string]$ServerName,
[string]$RuleName = ‘MyDefaultRuleName’,
[string]$IpAddress
)
$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.
Cheers,
Dave
Author: DB Dave
Currently the database & infrastructure guy at Timely Ltd. Between 2007 and mid-2014 I was the SQL Server Database Team manager at Trade Me. And before that I worked for a government department, a large private supermarket cooperative, and an IT training company. I’m originally from the sunny southern coast of South Africa, but since 2003 I’ve called New Zealand home. I do stuff with SQL Server, Azure, Business Intelligence, PowerShell, etc.
Sheikvara
+919840688822, +919003270444