Data Refresh on SQL Server on Microsoft Azure Virtual Machine

[RicSantos]

I have a scenario where the data refresh has been working manually and automatically, and from 07/07/2015 i started receiving errors:

"Last refresh attempt failed. Your gateway is offline or could not be reached. Ensure the computer on which the gateway is installed is not switched off or logged off during the scheduled refresh period."

According to documentation i should not need the Personal Gateway for a connection to SQL Server in Microsoft Azure Virtual Machine.

The scenario where this occurs, and was refreshing data before, is set up with a excel workbook stored in oneDrive for Business.The data is imported with the PowerPivot in Excel and with a connection to a SQL Server in Microsoft Azure Virtual Machines.

I set up the credentials in powerbi the same as the excel powerpivot connection, and this has been working before, and am now getting an error on automatic refresh and manual refresh operations.

The data is not being updated anymore.

[RicSantos · Accepted solution]

Just for an update on the topic...

No solution to this case... Just had to instal Power BI Personal Gateway  on Azure VM, and lost he ability to connect to SSAS tabular aZure on same machine.

The update and refresh are working with the personal gateway as explained in https://support.powerbi.com/knowledgebase/articles/649846-power-bi-personal-gateway

Thank you all for your support and suggestions.

[jason435]

Although I am using PostgreSQL in an Ubunty VM on Azure, I think I am in the same situation.

If I want to upload a Power BI report to powerbi.com and refresh it, I have to install a data gateway in an Azure VM? There is not way to open the firewall up to Power BI so I don't have to pay for another VM.

[andre]

I am sorry, this setting is for SQL Azure PaaS and not VM... I just realized I misunderstood your issue. Is it possible that this is firewall issue on the VM?

[PBI77]

are there any guidlines how to connect to a SQL DB on Azure VM. I have tried all sorts but seems like i cant get it to work

[andre]

you would connect to a sql db in azure vm the same way you would connect to a sql db on premise.  if you cannot connect, most likely your networking/firewall is making it difficult for you so i would escalate this issue to your network group

[Alay]

I believe a SQL Database on an Azure VM is IaaS and Power BI requires a data gateway to connect to such a data source. I am also interested to see when Microsoft Power BI Development team plans on addressing this? Refreshes depend on a personal gateway now which requires laptops to be on for the refreshes to work. This doesn't make sense....

http://blogs.msdn.com/b/powerbi/archive/2015/07/07/refresh-for-on-premises-sources-is-here.aspx

Does this help?

Tks & Rgds,

Alay

[andre]

You should make sure that Allow access to Azure services is set to "ON" for the SQL Azuyre Server that is housing your database.

This setting is located in "Firewall Settings" section of your SQL Azure Server properties.

[ashishrj]

Yes you are correct! Data in Sql which is on Azure VM does not need a gateway. But your Azure VM needs to be in running state while refreshing data.
Also check your SQL server login credentials. It might have been expired. So reset it and keep your Azure VM running and then try refreshing.

[RicSantos]

@ashishrj thank you for your reply!

Unfortunately none of the cases u suggested happened, i made sure that either the credentials and the AZURE VM was running.

@andre Thank you but this is a VM with SQL and i could not find any "Allow Azure Services" on the machine settings.

Still hoping to get a solution from @powerbiteam because all reported was working before the latest updates.