Forum Discussion
SQL Azure Import Data Refresh
I finally figured it out: the connection FQDN included the word "secure".
This won't allow server-side refresh: <database name>.database.secure.windows.net.
This will allow servers-side refresh: <database name>.database.windows.net
Both FQDNs seem to work otherwise. The 'secure' setting was used to enable auditing, but I don't think it is required anymore.
Problem solved. Thanks everyone.
-Jeff
Thanks Bhavesh. That is what I expect to happen.
Here is the challenge: when I go in to setup the credentials and schedule the refresh, instead I see this. This is a blank PBIX file with one connection to a Azure SQL DB.
Why am I being prompted for a gateway? Power BI should be able to refresh directly (which it does from the desktop).
You have to have a gateway for setting up the schedule refresh in PowerBI service.
Download the Personal Gateway and Set up connection for the first time.
Why do I need a gateway? The data is in the cloud, and so is powerbi.
According to this FAQ, I shouldn't need one:
Question: Do I need a gateway for cloud data sources like Azure SQL Database?
Answer: No! The service will be able to connect to that data source without a gateway.Any idea what I'm doing wrong?
PBI Desktop is a standalone product and PBI Service is a cloud product. if you connect to Azure SQL Database from PBI Service, You can not import the data and it is always a direct quey mode connection.
Since, you require importing of data, You need to use PBI Desktop where data is imported and loaded into the data model. You can upload this PBI desktop file to the PBI Service and if you would like to schedule refesh this file, You have to install gateway.
you also need to refresh PBI Desktop file manually to get the latest data before you can decide the time of the schedule refresh in PBI Service.
I have other examples where I connect and import data to PBI Service - for example, connecting to CRM via ODATA. No gateway required. Or connecting to Excel in O365. Again, no gateway. Both examples import data into Power BI and there is no DirectQuery option.
In this example, PBI Service would connect via the personal gateway to my machine, which then reaches back out to the internet to access Azure SQL. Why wouldn't the PBI Service just connect to Azure SQL and bypass the gateway?
It seems that the PBI Service thinks I'm asking it to connect to an on premises SQL database, and therefore asking for the gateway service.
I don't mean to be argumentative Bhavesh... just trying to find a solution here that doesn't require a gateway to be online.