Update Azure SQL Firewall Rule using PowerShell (update 2018)

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.

Note: There are two categories of Azure PowerShell scripts/cmdlets. There are Resource Management cmdlets and Service Management cmdlets. I will describe how to perform this task with both below:

Service Management (the older way)

1. Install and Configure https://github.com/Azure/azure-powershell#installation

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

Resource Management (the newer way, update 2018)

1. Install Azure PowerShell RM cmdlets module https://www.microsoft.com/en-us/download/details.aspx?id=30339 (aka "Azure Rights Management Administration Tool")

2. Execute `Login-AzureRmAccount`

3. To execute the firewall script without needing to log in each time, you'll likely want to create an Azure Active Directory Application and Service Principal, and store the credentials for that AD App on disk. The AD Application that you create will be available to any subscription associated with that AD, however when you execute the `New-AzureRmRoleAssignment` command, that will be specific to the subscription that is in context. Use the following commands to view/set the active context (note: take note of the TenantId, we'll need it later):
Get-AzureRmSubscription (this will list subscription info, use the Id/SubscriptionId in the Set-AzureRmContext command to make that subscription the active context)

Note: It is not required to create a AD Application. You can alternatively store the credentials for your personal username on disk, however if/when they are loaded, that process will have access to everything your user has access to.

4. With the correct subscription in context, follow these steps to create an Azure AD App and SP (Note: If you want to use the AD app/credentials that will be stored on disk for multiple subscriptions, you'll need to execute `New-AzureRmRoleAssignment` for each subscription.):

$password = convertto-securestring {provide-password} -asplaintext -force
$sp = New-AzureRmADServicePrincipal -DisplayName exampleapp -Password $password
... wait a few minutes ...
New-AzureRmRoleAssignment -RoleDefinitionName Owner -ServicePrincipalName $sp.ApplicationId

5. Export the credentials for that AD App to a file:

Get-Credential -UserName {applicationId} -Message 'Enter password' | Export-CLixml -Path "$(Split-Path -path $profile -Parent)\azure-ad-sp-cred.xml"

Note: if you are not using an Azure AD App and you are just storing the credentials for your username, replace the {applicationId} above with your username (which is likely your email address).

6. Now, before executing the `Set-AzureRmSqlServerFirewallRule` cmdlet in your script, you'll need to read those credentials from disk, and pass them to `Add-AzureRmAccount` like below (note: TenantId can be found by executing `Get-AzureRmSubscription`, you must be logged in for that cmdlet to work):

$cred = Import-Clixml -Path "$(Split-Path -Path $PROFILE -Parent)\azure-ad-sp-cred.xml"
Add-AzureRmAccount -ServicePrincipal -Credential $cred -TenantId "{TenantId}"

7. And now, finally, you can execute the `Set-AzureRmSqlServerFirewallRule` cmdlet. You'll want to make sure the appropriate subscription is in context when you execute the firewall rule for each server.

$url = "http://icanhazip.com/"
$webclient = New-Object System.Net.WebClient 
$ipResp = $webclient.DownloadString($url) 
$ip = $ipResp.ToString().Trim()

Set-AzureRmContext -SubscriptionId "{SubscriptionId}"
Set-AzureRmSqlServerFirewallRule -ResourceGroupName "{ResourceGroupName}" -ServerName "{ServerName}" -FirewallRuleName "{FirewallRuleName}" -StartIpAddress $ip -EndIpAddress $ip

Hope this helps,

Service being used to discover external IP address


Popular posts from this blog

Search iPhone Text Messages with SQLite SQL Query

Use Azure Functions to Execute a SQL Azure Stored Procedure on a Timer

How to Turn Off Microsoft Arc Touch Mouse Scroll Sound Vibration