Azure PowerBI public ip allow in network security group firewall

allow-powerbi-publicip-firewall-to-azure.ps1

# SEO: azure powerbi public ip allow in network security group firewall
# this code will get the latest Azure public IP's from Azure public cloud for PowerBI, and allow them to connect on port 5432 postgresql
# feel free to copy-paste for your needs.
# MS doesn't have a static URL or API for the latest IP's, so this script will find the right URL
# MS doesn't support Azure CLI from a Powershell Azure Function. I ended up running this as an Azure DevOps pipeline with a cron schedule every day.
# star this if it was useful for you :)

function Throw-WhenError {
    param (
        [string]
        $action,
        [string]
        $output
    )
  
    if ($LastExitCode -gt 0) { 
        Write-Error $output
        throw
    }
}

${env:NSG_NAME} = 'aks-agentpool-*******-nsg';
${env:RESOURCEGROUP_NAME} = 'MC_Foundat*********_westeurope'
${env:SUBSCRIPTION} = 'LEGO-*****'
${env:DOWNLOAD_URL} = 'https://www.microsoft.com/en-us/download/confirmation.aspx?id=56519'

Write-Host "DOWNLOAD_URL: ${env:DOWNLOAD_URL} NSG_NAME: ${env:NSG_NAME}, RESOURCEGROUP_NAME: $(${env:RESOURCEGROUP_NAME}), SUBSCRIPTION: ${env:SUBSCRIPTION}"

$downloadUrlHTML = (Invoke-WebRequest -Uri ${env:DOWNLOAD_URL}).Content

$publicIpsJsonURL = Select-String -InputObject $downloadUrlHTML "(?:(?:https))(?:\([-A-Z0-9+&@#\/%=~_|$?!:,.]*\)|[-A-Z0-9+&@#\/%=~_|$?!:,.])*(?:\([-A-Z0-9+&@#\/%=~_|$?!:,.]*\)|[A-Z0-9+&@#\/%=~_|$].json)" -AllMatches

Write-Host "Found JSON download URL: $($publicIpsJsonURL.Matches[0].Value)"

$latestAzureIps = Invoke-RestMethod -Uri ($publicIpsJsonURL.Matches[0].Value)

$latestAzurePowerBI_ips = ($latestAzureIps.values | Where-Object { $_.properties.systemService -Match "PowerBI" -or $_.properties.systemService -Match "PowerQueryOnline" }).properties.addressPrefixes

$ipv4 = $latestAzurePowerBI_ips | Where-Object { $_ -Match "^([01]?\d\d?|2[0-4]\d|25[0-5])(?:\.[01]?\d\d?|\.2[0-4]\d|\.25[0-5]){3}(?:\/[0-2]\d|\/3[0-2])?$" }
$ipv6 = $latestAzurePowerBI_ips | Where-Object { $_ -NotMatch "^([01]?\d\d?|2[0-4]\d|25[0-5])(?:\.[01]?\d\d?|\.2[0-4]\d|\.25[0-5]){3}(?:\/[0-2]\d|\/3[0-2])?$" }

if ($ipv4.Count -gt 0 -And $ipv6.Count -gt 0) {
    $output1 = az network nsg rule create  --name 'AllowPBI-ipv4' `
        --nsg-name ${env:NSG_NAME} `
        --priority "105" `
        --resource-group ${env:RESOURCEGROUP_NAME} `
        --access "Allow" `
        --destination-port-ranges '5432' `
        --direction "Inbound" `
        --source-address-prefixes $ipv4 `
        --source-port-ranges '*' `
        --subscription ${env:SUBSCRIPTION}

    $output2 = az network nsg rule create  --name 'AllowPBI-ipv6' `
        --nsg-name ${env:NSG_NAME} `
        --priority "106" `
        --resource-group ${env:RESOURCEGROUP_NAME} `
        --access "Allow" `
        --destination-port-ranges '5432' `
        --direction "Inbound" `
        --source-address-prefixes $ipv6 `
        --source-port-ranges '*' `
        --subscription ${env:SUBSCRIPTION}
    
    Throw-WhenError -output $output1
    Throw-WhenError -output $output2                               
}
else {
    throw "ERROR! Failed retrieving Azure public ipv4 / ipv6 IP's for PowerBI. Probably Microsoft's download page changed... ${env:DOWNLOAD_URL}"
}

Write-Host "Complete! NSG updated with $($ipv4.Count) IPv4 addresses and $($ipv6.Count) IPv6's addresses."