Powershell - Set Azure SQL Firewall for all Azure SQL Service Tag IP in a Region

 With one of my client we had to create a external data source between 2 Azure DB's but we were not happy to set Allow Azure services option to enable connectivity for 2 Azure DB's and open up Firewall to DB from all Azure resources so we went through the option of setting Allow Azure Services Off and whitelisting SQL ServiceTag IP's for Azure region where our DB's were hosted, Below is the script i created to accomplish the task and WestEurope is the region I used...

	#Script to retrive All IP's for SQL Service Tag in a Azure Region and whitelist it on a Azure SQL Firewall.
	#Created by Vinoth N Manaoharan
	#Version 1.0
	#Date: 30/12/2020
	#https://gallery.technet.microsoft.com/scriptcenter/Start-and-End-IP-addresses-bcccc3a9
	function Get-IPrangeStartEnd
	{
	<#
	.SYNOPSIS
	Get the IP addresses in a range
	.EXAMPLE
	Get-IPrangeStartEnd -start 192.168.8.2 -end 192.168.8.20
	.EXAMPLE
	Get-IPrangeStartEnd -ip 192.168.8.2 -mask 255.255.255.0
	.EXAMPLE
	Get-IPrangeStartEnd -ip 192.168.8.3 -cidr 24
	#>
	param (
	[string]$start,
	[string]$end,
	[string]$ip,
	[string]$mask,
	[int]$cidr
	)
	function IP-toINT64 () {
	param ($ip)
	$octets = $ip.split(".")
	return [int64]([int64]$octets[0]*16777216 +[int64]$octets[1]*65536 +[int64]$octets[2]*256 +[int64]$octets[3])
	}
	function INT64-toIP() {
	param ([int64]$int)
	return (([math]::truncate($int/16777216)).tostring()+"."+([math]::truncate(($int%16777216)/65536)).tostring()+"."+([math]::truncate(($int%65536)/256)).tostring()+"."+([math]::truncate($int%256)).tostring() )
	}
	if ($ip) {$ipaddr = [Net.IPAddress]::Parse($ip)}
	if ($cidr) {$maskaddr = [Net.IPAddress]::Parse((INT64-toIP -int ([convert]::ToInt64(("1"*$cidr+"0"*(32-$cidr)),2)))) }
	if ($mask) {$maskaddr = [Net.IPAddress]::Parse($mask)}
	if ($ip) {$networkaddr = new-object net.ipaddress ($maskaddr.address -band $ipaddr.address)}
	if ($ip) {$broadcastaddr = new-object net.ipaddress (([system.net.ipaddress]::parse("255.255.255.255").address -bxor $maskaddr.address -bor $networkaddr.address))}
	if ($ip) {
	$startaddr = IP-toINT64 -ip $networkaddr.ipaddresstostring
	$endaddr = IP-toINT64 -ip $broadcastaddr.ipaddresstostring
	} else {
	$startaddr = IP-toINT64 -ip $start
	$endaddr = IP-toINT64 -ip $end
	}
	$temp=""|Select start,end
	$temp.start=INT64-toIP -int $startaddr
	$temp.end=INT64-toIP -int $endaddr
	return $temp
	}
	Connect-AzAccount
	Select-AzSubscription '<subscriptionid>'
	#Retrive All the Ip's for a Service Tag in a Region
	$serviceTags = Get-AzNetworkServiceTag -Location WestEurope
	$sql = $serviceTags.Values | Where-Object { $_.Name -like "Sql*" -and $_.Properties.Region -eq "westeurope" }
	$iplist = $sql.Properties.AddressPrefixes
	$cnt = 0
	#Convert CDIR to IpAddress
	ForEach($ipaddr in $iplist)
	{
	$ipsplit = $ipaddr.split('/')
	#Write-Host $ipsplit[0] '--' $ipsplit[1]
	$IPRange = Get-IPrangeStartEnd -ip $ipsplit[0] -cidr $ipsplit[1]
	$rulename = 'WestEuropeSQLIP'+[string]$cnt
	$rulename
	New-AzSqlServerFirewallRule -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -FirewallRuleName $rulename -StartIpAddress $IPRange.start -EndIpAddress $IPRange.end
	$cnt = $cnt + 1
	}

view raw Set-AzureSQLServiceTagFirewall.ps1 hosted with ❤ by GitHub

Copyright © 2020 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.