Enable SQL Azure access from Azure WebJobs by adding firewall rules using PowerShell | José Quinto

Introduction

When we are developing custom Azure Web Apps or WebJobs, likely we will need access to Azure SQL database. Depends on the needs you will use Entity Framework, or direct Queries using Stored Procedures, but this is not the topic of this post. The topic today is how to allow our Azure WebJob to access to SQL Azure database, doesn’t matter what method we are using.

After some investigation, there are different ways to do that, I will share with you the different options I concluded:

The options

1. Allow access other Azure Services

   Security Note: this approach enables access to ALL AZURE SERVICES IN THE SAME REGION (even from other subscriptions).

2. Allow Outbound IP Addresses of the Azure Web App in SQL Azure using a specific firewall rule

   • Dynamic IP
     • Use TimerJob to add rules every certain time (if the IP changes)
     • Use Powershell and schedule a task every certain time (THIS IS THE SCENARIO APPLIED ON THIS POST)
   • Static IP (probably expensive, requires Premium App Service Plan)
     • Using ASE (other) and Add the Firewall rules manually only once

Read more…

Some useful references:

• Configure an Azure SQL Database server-level firewall rule using the Azure Portal)
• Reserved IP
• Azure Security fundamentals
• Create a server firewall rule in SQL Azure
• What is outgoing IP on Azure Web Apps

The approach and code

Now I will share the PowerShell script that you can configure with your Azure Web App and SQL Server details to add automatically the outbound IPs into SQL Azure firewall:

	<#
	.SYNOPSIS
	Add SQL Server firewall rules using ARM (the rew Azure Resource Manager).
	.DESCRIPTION
	The JQ.Azure.WebJob.SQL.AddFirewallRules.ps1 function will iterate thru all Web App listed and add a new SQL Server rule for each Outbound Id Address
	.PARAMETER Environment
	All configuration will be set up on this file
	.EXAMPLE
	This exports the entire termstore.
	./JQ.Azure.WebJob.SQL.AddFirewallRules.ps1
	.NOTES
	Requirements:
	- Azure Powershell 1.0:
	https://msdn.microsoft.com/library/mt619274.aspx
	Download file: http://aka.ms/webpi-azps
	How to install and configure Azure PowerShell: https://github.com/Azure/azure-content/blob/master/articles/powershell-install-configure.md
	.AUTHOR
	Jose Quinto: https://blog.josequinto.com
	#>
	####################################################
	######## CONFIGURATION PARAMETERS #########
	####################################################
	# Azure Admin
	$userName = "<user>@<tenant>.onmicrosoft.com"
	$pass = "<password>"
	# You can get it from https://manage.windowsazure.com --> Settings --> Subscriptions
	$SubscriptionId = "<guid>"
	# Web App Resource Group Name
	$webAppRgName = "<web app resource group>";
	# Web App Name
	$webAppName = "<web app name>";
	# SQL Server Resource Group Name
	$sqlRgName = "<sql server resource group>";
	# SQL Server Name
	$sqlServerName = "<sql server name>";
	# Define methods used for logging
	Function LogSection ($message) {
	Write-Host ""
	Write-Host "** $($message) **" -ForegroundColor "magenta" #-BackgroundColor "blue"
	}
	Function LogWaiting ($message) {
	Write-Host "$($message)... " -NoNewline -ForegroundColor "white" -BackgroundColor "blue"
	}
	Function LogInfo ($message) {
	Write-Host $message #-ForegroundColor "white" -BackgroundColor "blue"
	}
	Function LogWarning ($message) {
	Write-Host $message -ForegroundColor "yellow" #-BackgroundColor "blue"
	}
	Function LogError ($message) {
	Write-Host $message -ForegroundColor "red" #-BackgroundColor "blue"
	}
	Function LogSuccess ($message) {
	Write-Host $message -ForegroundColor "green" #-BackgroundColor "blue"
	}
	Function LogDuration ($startDate) {
	$duration = (Get-Date) - $startDate
	LogInfo "Script duration: $duration `n`n"
	}
	$cmdName = "Add-AzureRmAccount"
	if (Get-Command $cmdName -errorAction SilentlyContinue)
	{
	try {
	LogSection "Add SQL Firewall Rules from Web App outbound ips"
	$startDate = (Get-Date)
	LogInfo ("Script started: " + $startDate.ToString())
	# Azure Authentication
	LogWaiting "Authenticating on Azure"
	$secureAdminPassword = $(convertto-securestring $pass -asplaintext -force)
	$cred = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $userName, $secureAdminPassword
	Add-AzureRmAccount -Credential $cred
	LogSuccess "done"
	LogDuration $startDate
	# Setting Subscription Id
	LogWaiting "Setting subscription Id ($SubscriptionId)"
	Set-AzureRmContext -SubscriptionId $SubscriptionId
	LogSuccess "done"
	LogDuration $startDate
	# Get Web App OutboundIpAddresses
	LogWaiting "Get Web App OutboundIpAddresses"
	$webapp = Get-AzureRmWebApp -ResourceGroupName $webAppRgName -Name $webAppName -errorAction SilentlyContinue
	if ($webapp){
	LogSuccess "$webAppName was found."
	# Extract Outbound IPs
	$ips = $webapp.OutboundIpAddresses
	if ($ips){
	$ipsArr = $ips -split ','
	$ipsArr | ForEach {
	$index = $ipsArr.IndexOf($_)
	$ruleName = $webapp.SiteName + "-" + $index
	$startIp = $_
	$endIp = $_
	Write-Host "Adding IP $index to SQL Server rule: $ruleName , StartIP: $startIp , EndIP $endIp"
	$fr = Get-AzureRmSqlServerFirewallRule -ResourceGroupName $sqlRgName -ServerName $sqlServerName -FirewallRuleName $ruleName -errorAction SilentlyContinue
	if ($fr){
	# No changes on the IPs
	if (($fr.StartIpAddress -eq $startIp) -and ($fr.EndIpAddress -eq $endIp)){
	LogSuccess "$ruleName was already created and keep the same IPs ($startIp - $endIp)"
	} else {
	Set-AzureRmSqlServerFirewallRule -ResourceGroupName $sqlRgName -ServerName $sqlServerName -FirewallRuleName $ruleName -StartIpAddress $startIp -EndIpAddress $endIp
	LogSuccess "$ruleName was already created but IPs has been updated to ($startIp - $endIp)"
	}
	}
	else{
	New-AzureRmSqlServerFirewallRule -ResourceGroupName $sqlRgName -ServerName $sqlServerName -FirewallRuleName $ruleName -StartIpAddress $startIp -EndIpAddress $endIp
	LogSuccess "done"
	}
	}
	}
	else{
	Write-Host "No Outbound IPs founded for $webAppName " -ForegroundColor Red
	}
	}
	LogDuration $startDate
	}
	catch
	{
	Write-Host "Errors found:`n$_" -ForegroundColor Red
	}
	}
	else{
	LogError "$cmdName doesn't exists. Please install latest version of Azure Powershell 1.0: http://aka.ms/webpi-azps"
	}

view raw JQ.Azure.WebJob.SQL.AddFirewallRules.ps1 hosted with ❤ by GitHub

Important notes on the code:

• Change all the configuration parameters including Azure Admin, Web App and SQL info.
• Run the script and enjoy!

  Outbound Public URL aren’t Static by default, so probably they could change over the time (is not usual but can happen). So, I recommend some daily task to check every day and add the new IPs if needed. As the code is ready to update if the IPs are different. (read more here)