Are you in mobility and you have lots of SQL Azure DB Servers to manage? Does your public IP address change often and you’re sick of having to manually change the SQL Azure firewall rules?
Good news, I’m going to show you a PowerShell script to automatically add a firewall rule to ALL your SQL Azure servers in ALL your subscriptions for your current public Internet IP Address.
Setup your WA PowerShell subscriptions
The first part after downloading the Windows Azure PowerShell package is to setup the subscriptions you have access to. You will need to do this process only once, since the configuration settings will be stored in your profile, but perhaps you would like to revisit it later to add more subscriptions.
Configuring subscriptions by importing a .publishsettings file
The fastest way to setup the subscriptions is by importing a .publishsettings file containing an encoded management certificate data and subscription Ids:
1) Download your publish settings file:
PS C:\> Get-AzurePublishSettingsFile
This will open a browser that, after introducing your LiveId, will automatically download your .publishsettings file. This file contains credentials to administer your subscriptions and services, so be sure to store it in a secure location or delete after use.
2) Import your .publishsettings file:
PS C:\> Import-AzurePublishSettingsFile MyAzureCredentials.publishsettings
These settings will be stored inside “C:\Users\<UserName>\AppData\Roaming\Windows Azure PowerShell” folder.
Configuring subscriptions by using self-signed management certificate
Another way to setup your subscriptions would be to use your own self-signed management certificates, to avoid the automatic creation of management certificates in all your subscriptions and giving you more control on which subscriptions you are going to manage via PowerShell.
1) Create and Upload a Management Certificate for Windows Azure. Follow the instructions described in this MSDN article.
2) Run the following PowerShell script to access your subscription from PowerShell:
$subscriptionId = '<type your subscriptionId here>'$subscriptionName = '<type a subscription name here>'$thumbprint = '<paste your management certificate thumbprint here>'$mgmtCert = Get-Item cert:\\CurrentUser\My\$thumbprint
Set-AzureSubscription -SubscriptionName $subscriptionName -SubscriptionId $subscriptionId -Certificate $mgmtCert
You can repeat this operation for each subscription you want to manage from PowerShell.
Finally, with both ways of configuring your Azure Subscriptions, you can verify which subscriptions you have setup by running “Get-AzureSubscription” Cmdlet.
One script to rule them all
Now that we have setup the subscriptions, the intention is to create a firewall rule in ALL the SQL Azure servers under ALL my subscriptions for my current public IP address, in order to manage them by using SQL Server Management Studio or whatever other tool.
Based on Alexander Zeitler’s blog post on the matter, I have added some modifications to build the following script that you can save in .ps1 file (I have called it RuleThemAll.ps1 
# Set a RuleName
$ruleName = "David Laptop"# Get your public Internet IP Address$externalIP = (New-Object net.webclient).downloadstring("http://checkip.dyndns.com") -replace "[^\d\.]"
# Loop all your subscriptions
Get-AzureSubscription | ForEach-Object { Select-AzureSubscription $_.SubscriptionName
# Loop all your SQL DB servers
Get-AzureSqlDatabaseServer | ForEach-Object {$rule = Get-AzureSqlDatabaseServerFirewallRule -ServerName $_.ServerName -RuleName $ruleName
if (!$rule) {New-AzureSqlDatabaseServerFirewallRule $_.ServerName -RuleName $ruleName -StartIpAddress $externalIP -EndIpAddress $externalIP
}
else {Set-AzureSqlDatabaseServerFirewallRule $_.ServerName -RuleName $ruleName -StartIpAddress $externalIP -EndIpAddress $externalIP
}
}
}
After a while, you will have the rule enabled in all your servers in all your subscriptions.
Hope this helps,
David Rodriguez
