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
Powershell script: https://gist.github.com/aaronhoffman/d5efc68d89995017caa4
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
Service being used to discover external IP address