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:
2) Create a new Data Source on top of the previously created Gateway as described here: Create a Data Source and Enable OData Feed in 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.
Hi Bryan,
yes, just tested it, also works with HTTPS
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
-gerhard
Pingback: Creating a Demo Power BI Data Gateway using an Azure Virtual Machine - Data Otaku - Site Home - MSDN Blogs
Power BI + Azure VM that intalled SQL work fine like what you said.
My Problem is , when i use power query on my home PC and want to connect to oDataFeed
I receive the below error.
DataSource.Error: OData: Request failed (The remote server returned an error: (503) Server Unavailable.):
I think it is related to the gateway is on the VM and my home PC is just different network.
Any idea how can i solve 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?
Hi Gerhard
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.
However, i am confused.
my VM has hostname:
xxxx.cloudapp.net
and my OData URL is provided by powerbi and it is
https://lxxxxhybridproxy.powerbi.com/ODataService/v1.0/MyODataServiceName
Then i go to my home PC that has installed Excel
i can telnet to the port 8085 and 8051 on xxxx.cloudapp.net
But i cant telnet to the port 8085 and 8051 on xxxxhybridproxy.powerbi.com
I find that i can use Office365 to sign in the power query
But when the power query to load the data model. It has the below.
What else i miss ? Is it related to my Office 365 account setup ?
”
OData: Request failed (Unable to connect to the remote server): Unable to connect to the remote server
Details:
https://lemonade.hybridproxy.powerbi.com/ODataService/v1.0/Lemon…
“
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
Thanks a lot
Let me give it a try again .
Yes it is typo. Port number is : 8085
Thanks a lot
Hiya Gerhard
I finally make it too!!!
I finally know my issue.
both my vm hostname and the local PC hostname are the same. That’s why it doesn’t work. >_<
Thanks a lot your help again and the post. It is awesome
Billy
Hi Billy,
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
Can you pls let me know the steps how you resolved the issue?
Thanks
Santhosh
Oh my! Brilliant!!! This worked for me too.
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.”
Had tried internal IP/External IP/Server Name
So here were the steps I did to setup:
1) Deleted all my gateways (only had two)
2) VPN to my DB server (remember this is a VM box)
3) On the DB Server
a) Open browser
b) Log into Office 365
c) Go to PowerBI Admin
d) Add Gateway
e) Uninstalled the Data Management Gateway Configuration Manager
f) Installed Data Management Gateway Configuration Manager
g) Went to Data Sources
i) Enable Cloud Access is checked
ii) Selected the .Net Framework Data Provider for SQL Server OR Microsoft OLE DB Provider for SQL Server
iii) Entered [MyServerName]
iv) Entered DB (the actual schema inside the DB)
v) Changed Credential Type to ‘DataBase’
Voila!!
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.
In Powerquery i can see the table name data is not getting retrieved.
Any help to resolve this will be of great help.
Thanks
Santhosh
Hi Santhosh,
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?
these are the most common issues
-gerhard
Hi,
FYI I had to add the hosts entry in order to be able to load data (but not needed for previous steps)
Thanks for the post!
Dude. Love the posts. Do you think this approach will also work with the new Power BI Analysis Services connector?
I guess so but I have to admit that I have not tested it yet
As soon as I get my hands on the SSAS connector I will test it and very likely also blog about it
Greg Galloway already wrote a blog post on how to setup the SSAS connector which may also be of interest for you:
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=27
-gerhard
Pingback: Using Power BI to access on-premise data | James Serra's Blog
Pingback: Using Power BI to access on-premise data - SQL Server - SQL Server - Toad World
Pingback: Using Power BI to access on-premise data | blog1