Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rpm19841
Frequent Visitor

Portal won't allow me to setup scheduled refresh

Hi,

 

I have data stored in SQL Server (VM hosted on Azure). 

 

I setup the connection to this via Power BI desktop ('Import' mode), published it to the Power BI service. All good.

 

But now i would like to update this data hourly/nightly/etc, but the UI won't allow it. (see screenshot)

 

What am i doing wrong? Do i have to setup a gateway? It's in Azure, not on-prem, so i wouldn't think so? The documentation/forums related to see are a bit confusing.

 

 

 

Untitled.png

 

 

1 ACCEPTED SOLUTION

Hi @rpm19841

 

Ok my mistake in that it will not resolve to it's own Public IP Address.

 

Another option which might work is to edit your Hosts File. In here you can give it a simple name which will override and resolve to the IP Address via the name.

 

So where you have your Power BI Desktop you could put the following in your HOSTS File

 

SQLServerVM    PUBLIC IP Address

 

EG

SQLServerVM 201.25.54.213

 

Then log into your Azure VM and put the following in your HOSTS File

EG

SQLServerVM 127.0.0.1

 

Then use the Servername of SQLServerVM in your Power BI Desktop and see if that works from the Desktop

And then once again edit or create a new Gateway Data Source in the Power BI Desktop and see if you can connect sucessfully?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

12 REPLIES 12
dearwatson
Responsive Resident
Responsive Resident

I think you need to be running the on prem data gateway in enterprise mode for schedule refresh... its one of the triggers for buying pro.

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

hi, thanks for your reply.

 

i'm on the pro trial. also - this isn't on prem, it's in the cloud via an Azure VM. Is it because it's on a VM and not Azure SQL that's it's still considered 'on prem'? Can you point to an article? I've struggled to find info on this, which is surprising because i figured this was a simple/common scenario.

 

 

Hi @rpm19841

 

There is often confusion around how the data refreshes.

 

In your instance even though you have your VM sitting in Azure, it is actually still recognized as an On-Premise Source. This is because when the connection is made it is made to a Server, then SQL Server Name, then Database. 

Whilst with an Azure SQL Server the connection is made directly to Azure.

 

So you will need to download, install and configure the Power BI Gateway. You can do this on your Azure VM and configure it as an On-Premise Gateway (Enterprise Features), after which you will then be able to refresh.

 

Here are the links you requested:

 

Data refresh in Power BI

On-premises data gateway

 

If there are any other questions please let me know.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ

 

I've installed the Gateway on the machine as suggested.

 

It connected fine in the "Manage Gateways" section.

 

However, when i go to "Scheduled Refresh" in my existing data source which i setup in Power BI desktop (on my local PC), it's still saying the same screen as below. I can't select "Use a data gateway".

 

I noticed on here it says:

 

"Server and database name have to match between Power BI Desktop and the data source within the on-premises data gateway gateway!"

 

Is this the reason it's not working?

 

When i configured the data source in Power BI service, i had to use "127.0.0.1" as the server name, but on my local PC when i set up the data source, it was the azure public name, e.g "myapp-sqlserver.cloudapp.net". They will never match up.

 

Does that make sense?

 

Many thanks for your help!

Hi @rpm19841

 

You are indeed correct that both names have to be identical for the Power BI Service to match the name in your Power BI Desktop File.

 

I would suggest changing it from 127.0.0.1 to the Server Name and see if that connects, because it is going via the On-Premise gateway which uses the Azure Service Bus it should be able to resolve the Server name?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ it wouldn't connect via the server name. I'm guessing because _inside_ the machine, it can't resolve the public server name?

 

I do have an inbound firewall in place, but like you said - it's connecting via the gateway so it shouldn't need any changes to it.

 

Any other suggestions?

Hi @rpm19841

 

What I would suggest doing is making sure that your Server name does resolve to the correct IP Address. As I have had issues in the past where the name resolution would be different due to it resolving in Azure vs resolving where your Power BI Desktop file is.

 

Another simple test is to find out what the current IP address is, and put that into the Server Name on both the Power BI Gateway & Power BI Desktop and see if that works? 

If it does then at least that is showing that it is working.

 

Next steps would then be how to get them both using the same name.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ

 

I tried using the current public IP (on machine, go to "what is my IP" on google), as you suggested. It works on Power BI desktop, but not in the Power BI data source settings. It's like the gateway connection isn't bypassing the firewall....

Hi @rpm19841

 

Ok my mistake in that it will not resolve to it's own Public IP Address.

 

Another option which might work is to edit your Hosts File. In here you can give it a simple name which will override and resolve to the IP Address via the name.

 

So where you have your Power BI Desktop you could put the following in your HOSTS File

 

SQLServerVM    PUBLIC IP Address

 

EG

SQLServerVM 201.25.54.213

 

Then log into your Azure VM and put the following in your HOSTS File

EG

SQLServerVM 127.0.0.1

 

Then use the Servername of SQLServerVM in your Power BI Desktop and see if that works from the Desktop

And then once again edit or create a new Gateway Data Source in the Power BI Desktop and see if you can connect sucessfully?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ

 

You put me on the right path. 🙂

 

I just added this to the hosts file:

 

127.0.0.1 ServerName

 

So now on the machine, when accessed via "ServerName" it resolves to the local IP.

 

All good now.

 

Thanks so much for your help!!

Hi @rpm19841


Glad to assist and get it working, the Gateway setup can be quite tricky at times.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Yeh I think an Azure VM is as you say "on prem" but sorry I dont have a specfic article at hand (and I could be wrong) - This big clue for me here is the fact it says "Personal Gateway" - I had so much trouble with personal gateway in the early days I swore I would never use it.

 

Strongly recommend using the gateway in enterprise mode... it will have logging and stuff too which might give you clues as to whats going on.

 

Sorry I cant give you more targeted examples.

 

Cheer

Greg

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors