Add's Azure PaaS network ranges to the ip routes so that traffic goes to VPN connection (connect to Azure SQL through VPN)

readme.md

Add's Azure PaaS network ranges to the ip routes so that traffic goes to the VPN connection of your choice

Usage

Add all SQL service ip ranges from FranceCentral (only IP V4)

.\VpnAureAddRoutes.ps1 -VpnConnectionName "controlvnet" -AzureResourceRegex "^Sql\.FranceCentral$"

Remove all SQL service ip ranges from FranceCentral

.\VpnAureAddRoutes.ps1 -VpnConnectionName "controlvnet" -AzureResourceRegex "^Sql\.FranceCentral$" -RemoveRoutes

Add all SQL service ip ranges from FranceCentral (only IP V6)

.\VpnAureAddRoutes.ps1 -VpnConnectionName "controlvnet" -AzureResourceRegex "^Sql\.FranceCentral$" -IpV6

Do not attempt to add all Azure ranges (wildcarding the regex) as there is a limit of 200 ip route tables in Windows. Be specific into what Azure Zones and type of services you want to map, or you will easily reach that limit.

VpnAzurePaasAddRoutes.ps1

param(
    [string]$VpnConnectionName, # VPN connection name
    [string]$AzureResourceRegex, # Regular expression for Azure resource names
    [switch]$IpV6, # Switch parameter for IPv6 routes
    [switch]$RemoveRoutes # Switch parameter to remove routes
)

$jsonUrl = "https://download.microsoft.com/download/7/1/D/71D86715-5596-4529-9B13-DA13A5DE5B63/ServiceTags_Public_20240219.json"

# Create a temporary file
$tempFile = [System.IO.Path]::GetTempFileName()

try {
    # Download the JSON file to the temporary file
    Invoke-WebRequest -Uri $jsonUrl -OutFile $tempFile

    # Read the JSON file from the temporary file
    $jsonContent = Get-Content -Path $tempFile -Raw
    $jsonDocument = [System.Text.Json.JsonDocument]::Parse($jsonContent)
} catch {
    Write-Host "Error occurred: $_"
    Remove-Item -Path $tempFile # Clean up temporary file
    exit
}

# Initialize an array to store the IPs
$ipRanges = @()

# Extract IP addresses
foreach ($value in $jsonDocument.RootElement.GetProperty('values').EnumerateArray()) {
    if ($value.GetProperty('name').GetString() -match $AzureResourceRegex) {
        foreach ($address in $value.GetProperty('properties').GetProperty('addressPrefixes').EnumerateArray()) {
            if ($IpV6 -and $address.GetString() -match ":") {
                # Add only IPv6 addresses
                $ipRanges += $address.GetString()
            } elseif (-not $IpV6 -and $address.GetString() -notmatch ":") {
                # Add only IPv4 addresses
                $ipRanges += $address.GetString()
            }
        }
    }
}

# Add or Remove VPN connection routes based on the switch
foreach ($ip in $ipRanges) {
    if ($RemoveRoutes) {
        Remove-VpnConnectionRoute -ConnectionName $VpnConnectionName -DestinationPrefix $ip -PassThru
    } else {
        Add-VpnConnectionRoute -ConnectionName $VpnConnectionName -DestinationPrefix $ip -PassThru
    }
}

# Clean up: Dispose of the JSON document and delete the temporary file
$jsonDocument.Dispose()
Remove-Item -Path $tempFile