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
https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

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,
Aaron


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.
https://azure.microsoft.com/en-us/documentation/articles/resource-manager-deployment-model/

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
http://icanhazip.com/








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