Configure Azure SQL Database firewall rules with PowerShell

Living in the third world comes with its set of issues. I have a terrible pair of ISPs that I have to live with. One is Airtel 4g and the other is You Broadband. Both have a habit of refreshing the public IP every few hours (sometime even twice within an hour). I don't get the logic. While everything else is fine since I use (and highly recommend) noIP.com. However I am a Azure developer and my life depends on access to Azure SQL Database. With the frequent IP changes one constantly needs to keep a browser tab open with the Azure SQL Server settings open to be able to edit and save the newly assigned IP.

It takes about 4-5 clicks and a sagely amount of patience to do this. In the middle of a debug both can be hard to come by. I usually recommend the "Rule of three" for automating something.

If you had to do the exact same task the third time, automate it. - master jatlh, 'oH.

So cooked up this little PowerShell script. For tl;dr crowd, you can go get it here.

If you are still reading lets dig in. The script does some nifty things. First it gets your public IP. I found this cool no-nonsense HTTP endpoint at IfConfig.me. Hat-tip for a great name for the tool. Exactly does what it says. Returns a simple text response in body with your publicly visible IP. Here's a fiddler trace showing all the gory details on the wire.

Then it checks if the given firewall rule name already exists, if it does that one is deleted and a new one is created. I needed this as I constantly need to update the settings and there are few of us devs using the same DB and all have their own firewall rules.

One thing to note is I have removed the PowerShell Confirm dialog from the Remove-AzureRmSqlServerFirewallRule commandlet. I know what I am doing and didn't need the pesky prompts. If you are worried of accidentally removing a rule remove the -Force parameter from the command. This script also ignores errors if any.

Next up, making sure this scripts gets called from a batch file whose icon sits happily in your taskbar. But that's for another post.

Ref:
Remove-AzureRmSqlServerFirewallRule
New-AzureRmSqlServerFirewallRule