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-AzureRmContext
Get-AzureRmSubscription (this will list subscription info, use the Id/SubscriptionId in the Set-AzureRmContext command to make that subscription the active context)
Set-AzureRmContext

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


Service being used to discover external IP address
http://icanhazip.com/








Comments

Azure DevOps said…
This comment has been removed by a blog administrator.
February 9, 2019 at 4:05 AM
Muralidhara Raju Indukuri said…
This comment has been removed by a blog administrator.
March 17, 2019 at 6:43 AM
Post a Comment

Popular posts from this blog

Search iPhone Text Messages with SQLite SQL Query

Image
While you're here, check out some of our interactive visualizations: Would you like to visualize your text messages? ------------------------------------------------------------------------------- In my experience, the iPhone text message search functionality is usually pretty awful. Especially if you're trying to find a text from a few years ago. Luckily, if you backup your iPhone using iTunes, your text messages are exported/stored in a SQLite database, and it is fairly easy to query. Here are the steps to SQL Query your Text Messages.   1. Backup your iPhone using iTunes.   2. Find the SQLite file that contains your text messages .     - in ~/Library/Application Support/MobileSync/Backup/* with a filename of 3d0d7e5fb2ce288813306e4d4636395e047a3d28   3. Ensure you have a SQLite Query tool ( SQLiteBrowser is pretty good ).   4. Open your SQLite Text Message DB File using your favorite SQLite query tool.   5. Execute t...
Read more

Configure SonarAnalyzer.CSharp with .editorconfig, no need for SonarCloud or SonarQube

Our goal was to use  SonarSource / SonarQube  static code analysis with the most "minimal" install/configuration footprint. We wanted the static code analysis to break/fail the build on the local developer machines as well as our CI/CD Azure DevOps environment if a rule was violated. SonarSource products usually rely on some external source,  SonarCloud /SonarQube to determine which rules to apply on the local dev machine, and to store the results. We wanted to eliminate these dependencies. SonarSource also provides a Visual Studio extension called  SonarLint  to help check the static code analysis rules within the Visual Studio IDE without needing an external source for the rules. The static code analysis rules SonarLint enforces are also defined in the  SonarAnalyzer.CSharp  nuget package. This nuget package is a set of  Roslyn Code Analyzers . In .NET Core, Roslyn Code Analyzers are triggered during a build if the nuget package is referenced by...
Read more

Edit Default Visual Studio 2012 Item and Project Templates

After adding a new file or project within a Visual Studio solution there are certain settings that I always update.  Follow the steps below to edit the default templates so these settings become the default. Visual Studio 2012 Project and Item Template files are located here: C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\ItemTemplates C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\ProjectTemplates Add "public" to all new C# class files: Edit the Class.cs file by adding the word "public" in front of the word "class" The Class.cs can be found here: C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\ItemTemplates\CSharp\Code\1033\Class\Class.cs After you're done, it should look like this: using System; using System.Collections.Generic; $if$ ($targetframeworkversion$ >= 3.5)using System.Linq; $endif$using System.Text; $if$ ($targetframeworkversion$ >= 4.5)using System.Threading.Tasks; $e...
Read more