Since Dev is my favorite part of DevOps I always try to automate the Ops as much as possible using scripts. Scripting manual work can also help prevent errors, especially when the work has to be done on multiple environments. In this case the scripts can be tested on the Development environment and safely executed on the Production environment. One of the recent tasks I’ve completed using Powershell scripts is limiting the access to Azure SQL server instances to our (Azure) App Service instances. By default Azure SQL server access is allowed from any Azure IP address, including those from other Azure tenants. Limiting this access can of course greatly reduce the attack surface. My script was to execute the following tasks:

1. Retrieve all unique IP addresses of all App Service instances in the subscription
2. Create an allow rule for each IP address in the list of unique IP addresses on a specified Azure SQL Server instance

An App Service instance has more than one outbound IP address. Outbound IP addresses are not dedicated to an App Service plan but can be shared with other Azure tenants. The only way to have a dedicated list of outbound IP addresses is to use the Isolated tier. Still, to limit SQL Server access to App Service IP’s is a great improvement to the standard setting that allows any Azure IP network access to the SQL Server. To retrieve the outbound IP addresses of all App Service instances the following Powershell command can be used:

$appServiceIps = (((Get-AzWebApp | Select possibleOutBoundIpAddresses -ExpandProperty possibleOutBoundIpAddresses).Split(',')) | Sort-Object | Get-Unique)

Since Powershell is based on .NET -ExpandProperty possibleOutBoundIpAddresses will select the property as System.String, of which the Split function is used to split the comma seperated list of IP addresses returned. Get-AzWebApp is used to select all available App Service instances, and some of the used IP addresses may be used by multiple App Service Plans. Therefore | Sort-Object | Get-Unique is used to filter the list of IP addresses to select only the unique ones.

The next step is to loop through the list of unique IP addresses and to create firewall rules in Azure SQL server. I’ve set the SQL server name and Resource Group as a script parameter so the script is more generic. I also make a distinction between rules managed by my script and manually created rules. I do so by using a rule name prefix so I can recognize which rules were created by my script and which were created by other means. So for this code to work the script needs the parameters $firewallRulePrefix, $sqlServerResourceGroupName and $sqlServerName. A new firewall rule is only created if a rule does not already exist for the IP address:

$currentRules = (Get-AzSqlServerFirewallRule -ResourceGroupName $sqlServerResourceGroupName -ServerName $sqlServerName) | where {$_.FirewallRuleName.ToString().StartsWith($firewallRulePrefix)}

$currentRuleNames = $currentRules | select FirewallRuleName -ExpandProperty FirewallRuleName

#Create new rules for App Service IPs
Write-Host
Write-Host "Creating firewall rules: "
Write-Host

foreach ($ip in $appServiceIps)
{
 $ruleName = $firewallRulePrefix + $ip.ToString().Replace('.', '')

 if ($currentRuleNames -eq $Null -or !$currentRuleNames.Contains($ruleName))

  $newRule = New-AzSqlServerFirewallRule -ServerName $sqlServerName -ResourceGroupName $sqlServerResourceGroupName -FirewallRuleName $ruleName -StartIpAddress $ip -EndIpAddress  $ip

  if ($newRule -ne $Null)
  {
   Write-Host "Firewall rule: $ruleName created"
  }
  else
  {
   Write-Host -ForegroundColor Red "Error creating rule: $ruleName"
  }
 }
 else
 {
  Write-Host "Firewall rule: $ruleName already exists"
 }
}

In my script I’ve enclosed the code in a function so I can call if for different environments and just specify the SQL server name as a parameter. I’ve used the following function definition:

function Create-AppServiceOutboundIpRulesForSqlServer 
{
   param(
  [parameter(Mandatory=$True)]
  [string]
  $sqlServerResourceGroupName,

  [parameter(Mandatory=$True)]
  [string]
  $sqlServerName,

  [parameter(Mandatory=$True)]
  [string]
  $firewallRulePrefix)

So that’s it. In my own script I also added some functionality for removing unused IP addresses. Outbound IP addresses may change when switching between pricing tiers. This of course means the script needs to be re-ran in order to add the new IP addresses to the list.