Using Power BI Data Management Gateway on Non-Domain Azure VM

UPDATE AUGUST 2014:There were some changes to the DMG in August 2014. Please refer to my new blog post which addresses the issues with the new version! However, I still recommend you to read this post first in order to fully understand the original issue!The new post can be found here.

I am currently preparing some demos and examples for Power BI. As you can expect for demos you do not want to put too much effort in building up any infrastructure so I decided to use an Azure VM to host my SQL databases and SSAS cubes. Keeping things simple the Azure VM is not joined to a domain which is fine for SQL where I can use SQL authentication, for SSAS I use msmdpump.dll. After everything was set up I wanted to install the Data Management Gateway to expose my SQL tables via OData to Power Query and Online Search.Bryan C. Smith recently published an article on that very same topic Creating a Demo Power BI Data Gateway using an Azure Virtual Machine but for some reasons it did not work for me. Further, as Bryan already mentions in the first paragraph, his setup is not supported and its also a bit of a hack (modifying hosts-file, and so on). So I started my own investigations and came up with another solution, which only uses out-of-the-box features and tools and is actually quite simple. Another thing to mention here is that it will (probably) not work for scheduled data refreshes but only for exposing the SQL database via OData and make it searchable in Power Query.Having that said, here are the steps to follow:

1) Setup the Data Management Gateway itself on the Azure VM as described here: Create a Data Management Gateway. This should work just fine and the Gateway should be in the “Registered”-state on the Azure VM and in “Ready”-state in the Power BI Admin Center:

Here you will usually receive an error when you want to enter credentials for the SQL Database:

By Clicking on the [credentials]-button a new window pops up. Please note that this is a click-once application that actually runs on your client and is independent of your actual browser!

If the Gateway is running on an Azure VM, or basically any machine which cannot be reached from your current client you will receive an error that a connection could not be established or something similar. Assuming you called your Azure VM “MyCloudServer” and is perfectly reachable via “MyCloudServer.cloudapp.net” you will receive an error saying that “MyCloudServer” (without “.cloudapp.net”) could not be resolved. Which is actually true as the correct server would be “MyCloudServer.cloudapp.net”. Unfortunatelly, this server name cannot be changed anywhere as far as I know. As the name cannot be changed we need to make the name somehow “resolveable”. Bryan manually modifies the hosts file and makes “MyCloudServer” point to the public IP address of “MyCloudServer.cloudapp.net”. This should usually work just fine, but somehow did not work for me. Also the public IP address may change if you reboot your Azure VM and so you would need to modify the hosts-file again.

So these are the findings we mad so far:– the Data Source Manager is a click-once application which runs on the client– the client must be able to resolve “MyCloudServer”

After some thinking I ended up with the following:The only machine in my scenario that can correctly resolve “MyCloudServer” is the Azure VM itself! So instead of running the Data Source Manager on my client I simply connected to the Power BI Admin Center from my server and repeated the steps from above there. Now everything works fine and we can proceed:This connectivity check is only done once and has no further impact (I am not 100% sure on this ). Though, the Username and Password are stored and used for all subsequent connection through the gateway, e.g. for OData access so make sure the user has the necessary access rights.

In the next step you can select the tables and views that you want to expose:

Those can then be searched and queried using Excel and Power Query from any client:

And that’s it – The simple trick is to run the Power BI Admin Center from the server itself and create the data source there!

Hope this helps everyone who is dealing with the same issue or wants to setup a demo environment too.

Nice post. I’ll put a link on my blog post so that if someone reads mine they can find yours as well. This tech is evolving fast so that I’m sure there will be more fiddling with these configurations over time.

One thing I was curious about was with your configuration, are you using HTTPS with the gateway service? If you do, are you still able to pull data all the way into Excel? Since I have to occasionally demonstrate this tech with customer data, I prefer to use a secure channel. I’ve found that to be the trickiest part of this configuration – probably due more to my own ignorance than anything else – but it would be interesting to know if your configuration allowed for HTTPS connections as well. Thanks.

UPDATE: ok, it DOES NOT WORK WITH HTTPS!
seems that for my previous test it was still using HTTP
with HTTPS I now get the error
“DataSource.Error: OData: Request failed (The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.): The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.”

I will investigate into this and come back to you if I have any updates on this

well, the OData feed is hosted in the cloud/Power BI therefor it does not make a difference where you or your sever is located as long as you are online

according to the error i would check the following things:
Is the Gateway itself working? Power BI Admin Center and Data Management Gateway?
Are the ports opened in you firewall (windows)?
have you created endpoints for your Azure VM?

So on the VM, i have go to the manage window azure portal to open both 8050 and 8051 port. Then i go to the VM firewall page and make sure the outbound rule are also available for 8085 and 8051 port too.

OK, just follow these steps:
1) create Azure VM
2) create Endpoint on the Azure VM for port 8050
3) create inbound and outbound rules in the Windows Firewall on that Azure VM for port 8050
4) create a Gateway in Power BI Admin Center and copy the Key
4) install Data Management Gateway on the Azure VM
5) configure Data Management Gateway with the copied key
6) configure Data Management Gateway to use HTTP (not HTTPS) and port 8050
7) check if Data Management Gateway on Azure VM is in Gateway Key Status = “registered” and Service Status = “Started”
8) check if Gateway in Power BI Admin Center is
9) create a data source in Power BI Admin Center – this has to be done on the Azure VM directly
10) enable OData for the data source
11) enter credentials and select tables to expose via OData
this should do the trick

I dont know if it was a typo or not but you sometimes referred to port 8050 and in the next sentence you used 8085 – please check those settings again, of course the ports all have to match otherwise it wont work

its also ok that you cannot telnet the OData feed
as long as you can telnet the Azure VM on the necessary ports this should be fine
This is the general dataflow from Azure VM to the Client via Power BI
Azure VM < --[Port xxxx]--> Power BI < --[OData]--> Client

Were you able to get data from Azure VM SQL instance in Power query. i am getting [DataSource.Error] OData: Request failed (Unable to connect to the remote server): Unable to connect to the remote server

We have our servers at Rackspace under VM and was running into the exact issue you described:
“Failed to verify gateway status. The remote name could not be resolved: ‘[MyServerName]’ – You can click “Cancel” to skip editing credential.”

I followed the steps everything work but when i try to add data in Excel getting error: [DataSource.Error] OData: Request failed (Unable to connect to the remote server): Unable to connect to the remote server.

according to the error i would check the following things:
Is the Gateway itself working? Power BI Admin Center and Data Management Gateway? Whats the state of the services?
Are the ports opened in you firewall (windows)?
have you created endpoints for your Azure VM?