Update Azure DB Firewall Rules with PowerShell

If you do Azure development and work from home like I do, you may find yourself repeatedly updating your SQL Azure Firewall settings to allow yourself to work with your database. 

This is especially true if your crappy ISP repeatedly disconnects you throughout the day and assigns you new IP addresses. I don't want to disparage any company in particular, but I really hope there's some bandwidth competition on the horizon.
So, I wrote a PowerShell script to fix this problem.  Now, I just say do the following:



Notice, the script checks the current rule and then replaces it, if needed.  If it is not, it will say "Current Rule Is Correct".

In order to use this script, you will need to download and "import" the WAPPSCmdlets from CodePlex using "Import-Module".

You will also need to fill in the 4 variables at the top of the script. Note that the Certificate you use must be a Management certificate that is installed into the Azure Portal.

#TODO: set these up as parameters (with defaults)
$ruleName = 'Rule Name' #Whatever you want to see in the Azure UI for this Rule
$server = '' # the server name (without the ".database.windows.net" on the end)
$subId = '' # The Subscription ID
$cert = (Get-Item cert:\\CurrentUser\My\[YOUR KEY GOES HERE]) #the key is the "Thumbprint" - use "dir cert:\CurrentUser\My" to find yours

function RemoveRule()
{
    Write-Verbose 'Removing Firewall Rule'
    Remove-SqlAzureFirewallRule -ServerName $server -RuleName $ruleName -SubscriptionId $subId -Certificate $cert | Format-Table
}

function AddRule($myIP)
{
    Write-Output 'Adding rule for IP: ' + $myIP
    New-SqlAzureFirewallRule -StartIpAddress $myIP -EndIpAddress $myIp -ServerName $server -RuleName $ruleName -SubscriptionId $subId -Certificate $cert | Format-Table
}

## Function to retrieve external IP address.
## the external address is retrieved from the
## title header of the webpage "www.myip.dk"

function Get-ExternalIP {
    $source = "http://automation.whatismyip.com/n09230945.asp"
    
    $client = new-object System.Net.WebClient
    
    #add the header that whatismyip.com suggests from (http://www.whatismyip.com/faq/automation.asp)
    $client.Headers.Add('user-agent', 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:12.0) Gecko/20100101 Firefox/12.0')
    
    $client.downloadString($source)
}

function Update-AzureDBFirewallRules
{
    # get current IP.
    $currentIp = &Get-ExternalIP
    $rules = (Get-SqlAzureFirewallRules $server -SubscriptionId $subId -Certificate $cert)
    
    $curRule =  $rules | ? {$_.RuleName -eq $ruleName}
    
    $shouldAdd = $false

    if ($curRule -ne $null) {
        $ruleIp = $curRule.StartIpAddress
        if ($ruleIp -ne $currentIp ) {
        
            Write-Warning "Current IP [$ruleIp] is incorrect, removing Rule"
            &RemoveRule
            $shouldAdd = $true
        }
        else {
            Write-Output 'Current Rule is Correct'
        }
    }

    if ($shouldAdd -eq $true)
    {
        &AddRule ($currentIp)
    }
}

Hope this helps some of you out there...