I believe that technically there's not much difference between connection to SQL Azure and SQL Server exposed on the internet. However the first one can be refreshed/access directly from Power BI and...
In addition to installing the gateway, you need to whitelist specific IP addresses from your on-premise network and this is not a onetime activity it seems. PowerBI is designed in a way that these IP’s keeps changing every week or so in background without prior information. Hence it is not a workable/reliable solution for any organisation securing its on-premises infrastructure. The usage of PowerBI is blocked for such organisations as there are no guidelines around specific list of IP addresses to be white-listed.
