Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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?
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.
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:
If there are any other questions please let me know.
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?
@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.
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?
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.