Update Azure SQL Firewall Rule using Powershell

Microsoft Azure SQL only allows connections from whitelisted IP addresses. My ISP seemed to change my external/public IP address daily. The combination of these two things was very annoying for me.

I assembled the powershell script below to make my life a little easier. It updates the firewall rule for each Azure SQL instance, and ensures they are all set to my current IP address.

1. Install and Configure Azure Powershell

2. Execute Get-AzurePublishSettingsFile to get the publishsettings file for the subscription you're going to be working with (a browser window will open, select the profile there. Note: you can add multiple publish settings.).

3. Execute Import-AzurePublishSettingsFile and provide the path to the file downloaded from the Get-AzurePublishSettingsFile command.

4. Get your Public IP address

5. Execute the following commands for each Subscription/Azure SQL database combination

Select-AzureSubscription -SubscriptionId "[yoursubid]"
Set-AzureSqlDatabaseServerFirewallRule -ServerName "[yourservername]" -RuleName "[yourrulename]" -StartIpAddress $ip -EndIpAddress $ip

Hope this helps,

Things you might want to check out:

Azure Resource Management vs Service Management
 - the script above uses the classic Service Management because it does not appear the Resource Management supports this functionality at this time.

Note: Even though, this script does not use Resource Management, If you want to use RM in an automated script (non-interactive), you'll need to create a Service Principal account in Active Directory, because the AzureRM cmdlets don't allow for automation with a Microsoft Account (ex: live.com) login flow
 - https://github.com/Azure/azure-powershell/issues/1309
 - http://blog.davidebbo.com/2014/12/azure-service-principal.html
 - https://azure.microsoft.com/en-us/documentation/articles/resource-group-authenticate-service-principal/

Service being used to discover external IP address

Post a Comment

Popular posts from this blog

How to Turn Off Microsoft Arc Touch Mouse Scroll Sound Vibration

Hacker News Dataset October 2016

VirtualBox Right-Click Windows Taskbar On Mac