How to connect to to Azure SQL from Powerbi when firewall is enabled

Question

I have an Azure SQL database and powerbi reporting connected to it - however its not able to sync due to firewall ip not whitelisted.

I added the first few ips that tried to connect, but it keep rotating.

I cannot enable "Azure services" on the firewall due to concern that other vms in azure can find the db and bruteforce the connection.

I am reading that the only way to get around this is to set up a VM with on premise gateway. However i wanna challenge that and ask if there are no other solution? I don't want to pay for a dummy VM.

Are there no ip list of powerbi that can be added?

Answer 1

Unfortunately based on my experience while setting up NSG , came to realize that there is no specific IP range for PowerBI similar to how we have for some Azure offerings corresponding to regions. So there are some options:

1. Have a VM whose IP you would whitelist in Server firewall and install On prem gateway and schedule report refresh via gateway

2. Have an automated job via some Azure offerings that would 1st whitelist the random IP every time you want to refresh and then refresh the dataset : This seems to be much more tedious w.r.t maintenance and management.

By making Allow Azure services to Off , you are actually removing the benefits of cloud to cloud and confining the Azure database within a Vnet like instance. You always have to juggle between security and Infra :)