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...
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #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 | |
| } |
Copyright © 2020 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.