Power BI Desktop With Azure VM SQL DB Data On One Drive Requires Gateway?

[PaulBFelix]

My data source is an Azure VM running SQL Server.  I used Power BI Desktop to save my *.pbix file to One Drive.  I then imported the *.pbix to Power BI using the "Get Data" online feature.  Problem is that the data can't be refreshed.  Instead I get a message saying I need to install the data gateway. 

I also have an Excel workbook with a PowerPivot model saved on One Drive.  This workbook data is also sourced from an Azure VM running SQL Server.  After using the online "Get Data" feature I am able to schedule a data refresh.  I verified that the data is getting pulled from the database.  There is no gateway installed.

Is this a bug?  Or am I missing something?

[PaulBFelix]

Same issue exist when using PowerQuery. 

- Excel Workbook

- OneDrive Personal

- PowerQuery

- Azure VM SQL data source

PowerBI will not allow refresh.  Says I need to install a gateway.  According to the "Data Refresh" documentation this should work.  Has anyone else been able to get this to work?

[ashishrj]

@PaulBFelix Even I am facing a similar issue. Have posted few days back but no proper solution to this yet. You can see my post here.

[PaulBFelix]

Thanks @ashishrj.  Do you have any idea how Power BI determines that the data source is located in Azure?  I'm guessing that the IP range is used.  Not sure if it matters, but I used the new portal (portal.azure.com) to create my VM.  The URL is formed a little differently since there is no cloud services.  If the domain is used, then perhaps Power BI would work as expected when sourcing data from a VM created in the old portal.  Just a guess.  However, since I am able to refresh data from my VM using a Power Pivot workbook there must be something else going on.

[PaulBFelix]

I went through the trouble of creating a VM on the old portal which uses cloud services.  Same issue.

[Seth_C_Bauer]

@PaulBFelix The difference in what "online" means is this. If you are using a VM hosted in the cloud, this is an Infrastructure as a Service (Iaas) and is treated as on-prem. If you are connecting to Azure SQL Database or Azure SQL datawarehouse these are Platform as a service (Paas) offerings and are considered "online". there are only certain situations that are completely dependent on what your datasource is where a gateway is not/is not required for powerpivot, and if you are connecting to a VM in an Iaas setup I would assume that would still require a gateway.

Here is a long list of scenerio's describing when a gateway is needed.

https://support.powerbi.com/knowledgebase/articles/474669-data-refresh-in-power-bi

[ashishrj]

@Seth_C_Bauer If this is the case then how come I was able to schedule refresh reports with data source as SQL Server in Azure Vm somewhere in July 2015 without the need to install Personal Gateway and still it refreshes daily without any error.

But now when I tried the same by creating new report, it asks for Personal Gateway. Are things changed?? Any update from Power BI team on the same?

[GuyInACube]

When using Azure SQL Database as the Data Source is a bit different.  It will allow for refresh without a gateway if you are a Pro user.  All you need to do is enter your credentials. 

If you are a Free user, it will want the Personal Gateway present to do a refresh.

In both cases, when i tried it, the file on OneDrive for Business, where it originated from, was not updated.

If you are getting an error saying the gateway was not reachable, it is because you are a Free user.

[PaulBFelix]

Hi @GuyInACube,  Are you talking about Azure SQL Datbase or Azure VM running SQL database.  This thread is about the latter.  I'm a pro trial user, and I get the gateway required message.

[GuyInACube]

Sorry, was talking about Azure SQL Database.  Didn't see the VM part.

Although that made me think of something.  It could come down to what you are putting in the Server name piece.  I'd have to validate, but we use the same source for Azure SQL Database vs. On-Prem.  It really comes down to the server name.  Azure SQL Database is a well known domain.

*.database.windows.net

The FQDN for Azure VMs is *.cloudapp.net.

Are you doing something like entering the FQDN within Power Pivot, but the machine name only in PBI Desktop/Power Query?  

myserver.cloudapp.net

vs. 

myserver

I'm wondering if that is causing the behavior to be different.  If it is the full FQDN for both, then i'm not sure.  But the FQDN for Power Pivot, and machine name for PBI Desktop/Power Query could explain it.  For cloudapp.net we know that is in the cloud.

[PowerBIGuy]

@PaulBFelix Are you sure you don't have a Personal Gateway installed. I've reproduced your scenarios and in all instances have been asked to install a personal gateway. Do you use a personal gateway for any other of your datasources? I don't see how storing a PowerPivot file in one-drive connected to a on-perm SQL server would bypass the need for a gateway.

[PaulBFelix]

Hi @PowerBIGuy.  I agree, something doesn't add up.  I sent a private message with my email address.  Let me know when you can meet, and I'll send a goto meeting invite.  I have never installed a gateway on any of the systems I am using to test with.

[PaulBFelix]

Thanks @Seth_C_Bauer.  Your explanation makes sense, but there are a couple things that don't add up.  Please let me know if you happen to know what is going on with these two scenarios.

1.  @ashishrj post linked below describes this scenario working intermitently.

http://community.powerbi.com/t5/Service/SQL-Server-in-Azure-AM-requires-Gateway/m-p/8119#M3615

2.  PowerPivot with an Azure VM SQL Server data source refreshes without the need for a gateway.  I have this scenario working today and can verify that the refresh request are hitting the source database with is in an Azure VM running SQL Server.

Thank you in advance for taking the time to reply.

[Seth_C_Bauer]

@PaulBFelix valid points, both. The only thing I can come up with is that a file on OneDrive behaves as an online source in itself and thus doesn't need the gateway. The reference that the support site had to datasources was updated, and no longer includes the "online" and "offline" datasources. Obviously, there is much confusion around this area (including me). I'm going to try to raise visibility on this by including a couple admins to see if we can get a better answer to the "Online vs. Offline" discussion especially as it relates to the scenerio's found here maybe that will help shed some light on things.

@Sandy, @MiguelMartinez and not an admin, but @GuyInACube cuz he's awesome - could anyone point us to a missed blog or provide some clarfication around this? - Thanks in advance.

[PaulBFelix]

Here's a bit of info that makes this even more interesting.  I can refresh a dataset linked to an Excel PowerPivot model saved to OneDrive which sources data from a server running SQL Server outside of Azure without a gateway.  I totaly didn't expect that to work without a gateway, but it definately works.  I can trace the request coming from Power BI when I initiate a data refresh.

I guess PowerPivot data models for whatever reason do not require a gateway.  They use the SQL native client provider. 

[PowerBIGuy]

A Azure VM with SQL server loaded is still consider a on-prem SQL server. Hence the need for a personal gateway.

[PaulBFelix]

Are you sure about that?  If an Azure VM requires a gateway, then why does it not requie a gateway when using powerpivot?