Use the data gateway to connect to SQL Azure databases

One question that comes up  regularly is how to use the firewall option of SQL Azure to open access to just Power BI. Now to use Power BI in general you can use the “enable azure services” option but for some customers this is not enough, they want to control the specific hosts (ip address) that can connect. Unfortunately at the time of writing Power BI doesn’t support having a single IP address, due to the elasticity of the service the connection can shift between the internal machines. This means that you need to use the whole Azure IP range for that data center in the firewall (as described here), again this might not be enough for some customers. Luckily the data gateway team implemented a feature not known by many (hence my blog post today) and they enable you to use a gateway for SQL Azure databases instead of going direct using the cloud refresh services that is part of Power BI.

Let’s see how this works. First I log into Power BI and set up a data source on my gateway that points to my SQL Azure database:

Then I create a report in PBI desktop that connects in DirectQuery mode to that same SQL Azure database:

And then a very simple report:

Now when publishing this report I get a question from Power BI desktop, it detected I have access to a gateway that has my data source configured, so I can choose whether I want to use the gateway or if I want to connect directly to the cloud data source. In my case we select my gateway:

So far so good now all we need to do is make sure the IP address the Data Gateway is running on is opened up in the SQL Azure Firewall. For now I am greeted with this error message in Power BI when look at the report:

Now I go into the Azure portal, SQL Azure and open the firewall with the IP address of my machine (notice the “Allow Azure services” option is turned off):

Now I refresh my report in PBI and get greeted with a nice report:

Of course there will be some additional latency as you are adding an additional service in the mix, it is probably a good idea to run a VM with the gateway in the same data center as your SQL Azure database just to keep the hops as shorts as possible.

Just to note: this only works with SQL Azure databases right now, no other datasource