Dutch Data Dude Data Nerdiness


Azure SQL firewall settings for Power BI refresh

28 Apr 2020

I do not claim that what is included in this post is the perfect way of doing things. However, some things related to Azure SQL firewall settings for Power BI data refresh caught me by surprise recently so I figured I would just write it down...

The scenario

You have built a Power BI dashboard that takes data from Azure SQL and want to have this report automatically refreshed in the Power BI Service.

Data refresh scenarios in Power BI

There are two ways of refreshing data in Power BI: the classic Power BI refresh and the refresh using a Dataflow. I will discuss the options separately because they have separate impact on the firewall settings on your Azure SQL database.

'Classic' Power BI Refresh

What I mean with 'classic' Power BI Refresh is the refresh that you can configure without using a Dataflow. You will find it on your dataset settings:

Now for this to work you will need to enter the credentials and allow Power BI to reach the Azure SQL database through the firewall. This is where the fun starts. If you have a new Azure SQL database you will not have touched the firewall at all or allowed your office IP through to be able to access it from Power BI Desktop and build a report.

If you enter your credentials on the Dataset settings page in Power BI and try to refresh, you will get an error because Power BI is not allowed through the firewall. In order to allow this there are a couple of options:

  1. Allow Azure services and resources access to the Azure SQL Server. You will find this button in the Azure Portal on the Azure SQL Server firewall configuration. Set it to 'Yes' and your refresh works. This is what is recommended in the official documentation. Sometimes, however, this is not an option since it opens the Azure SQL Database for more than just Power BI - it would allow any Azure Service to communicate to the database after authentication. For example, Azure Data Factory could now be used against this Azure SQL Database. This might be just what you want, or not, it depends on the security rules in your organization. It is the easiest and my preferred solution.
  1. Alternatively, you could use VNets and the Power BI Gateway (in a VM) to pull this off without opening up the firewall to all Azure Services, as it explained here and here. This is more restrictive but has the downside of a more complex and less cost-effective solution since you have to set up a VNet and a Power BI Gateway in a VM.
  2. Lastly you could figure out the IP addresses used by Power BI and allow those to go through the firewall. Downside is that those IP addresses can change without notice and you would have go in and manually make changes for the refresh to work. I would not recommend doing it, but if you want to try here is a script.

Dataflows

If you use Dataflows you can still use the options described above. However, there is a third and more hacky way of doing things: a script or Azure Automation Runbook.

Azure publishes the IP address ranges used for some Azure Services here. Unfortunately, Power BI 'classic' refresh uses IP ranges that are not documented here. Note that it is published per cloud - so if you are not in the public cloud you will need to download a different file. With this file you can do a manual update of the firewall (similar to #3 above) or try to do it automatically like I did below. If the IPs change you can use this file to retrieve the changes and make updates accordingly.

For the automatic solution you could write a script or use Azure Automation. I wrote a very hacky script which downloads the file mentioned above for public cloud, parses it and adds the rules for Data Flows to the Azure SQL firewall. Again, it is very hacky, but it works. You can either run this script manually, schedule it or plug this in Azure Automation to run it automatically. You can get the script here or read it below. Remember to use this at your own risk. I am not responsible for you not being able to access your Azure SQL database anymore because of this script.

#DutchDataDude.com
#Code is provided as is.
Install-Module Az.Network -Scope CurrentUser

#CONFIG
$resourcegroup = "[ResourceGroupForAzureSQLDatabase]"
$server= "[AzureSQLServerName]"
$subscriptionId = "[SubscriptionID]"
$location = "[LocationForResourceGroupAndAzureSQLServer]"

#this is the URL for public clouds, for other clouds use appropriate URL
$url = "https://download.microsoft.com/download/7/1/D/71D86715-5596-4529-9B13-DA13A5DE5B63/ServiceTags_Public_20200420.json"

#source: 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 
}


# Sign-in with Azure account credentials
Connect-AzAccount
$context = Set-AzContext -SubscriptionId $subscriptionId
$output = "$PSScriptRoot\servicetags.json"

#download the service tags and convert to Json
Invoke-WebRequest -Uri $url -OutFile $output

$j = Get-Content $output | ConvertFrom-Json

#remove all firewall rules for PowerQueryOnline
$existingrules = Get-AzSqlServerFirewallRule -ResourceGroupName $resourcegroup -ServerName $server
foreach ($rule in $existingrules) {
    if ($rule.FirewallRuleName -match "PowerQuery") {
        write-host "Removing rule " $rule.FirewallRuleName
        Remove-AzSqlServerFirewallRule -ResourceGroupName $resourcegroup -ServerName $server -FirewallRuleName $rule.FirewallRuleName -DefaultProfile $context
    }
}

$v = $j.values
foreach ($val in $v) {
    if ($val.name -match "PowerQuery") {
        $addresses = $val.properties.addressPrefixes;
        foreach($address in $addresses) {
            $split = $address.Split('/')
            $startend = Get-IPRangeStartEnd -ip $split[0] -cidr $split[1]
            $end = $startend.end
            $start = $startend.start
            $rulename = $val.name+":"+$start+"-"+$end

            #add rules
            write-Host "Adding " $rulename
            New-AzSqlServerFirewallRule -ResourceGroupName $resourcegroup -ServerName $server -FirewallRuleName $rulename -StartIpAddress $start -EndIpAddress $end -DefaultProfile $context
        }
    }
}

I hope this helps. Let me know if I missed anything!

Related posts