cancel
Showing results for 
Search instead for 
Did you mean: 
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

Accepted Solutions
Super User IV
Super User IV

Re: Portal won't allow me to setup scheduled refresh

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

Re: Portal won't allow me to setup scheduled refresh

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
rpm19841
Frequent Visitor

Re: Portal won't allow me to setup scheduled refresh

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.

 

 

Highlighted
dearwatson Responsive Resident
Responsive Resident

Re: Portal won't allow me to setup scheduled refresh

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
Super User IV
Super User IV

Re: Portal won't allow me to setup scheduled refresh

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
rpm19841
Frequent Visitor

Re: Portal won't allow me to setup scheduled refresh

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!

Super User IV
Super User IV

Re: Portal won't allow me to setup scheduled refresh

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
rpm19841
Frequent Visitor

Re: Portal won't allow me to setup scheduled refresh

@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?

Super User IV
Super User IV

Re: Portal won't allow me to setup scheduled refresh

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
rpm19841
Frequent Visitor

Re: Portal won't allow me to setup scheduled refresh

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....

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors