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
Anonymous
Not applicable

Dataset refresh not working from Azure SQL

We have created a report in Power BI desktop that loads data from Azure SQL (Not direct query). We published the report under the Power BI service and the refresh data set worked for a while.

 

Recently it stopped working for some reason and this is what shows up on the Dataset settings page:

 

https://www.dropbox.com/s/vwju552dymtqkc4/Power%20BI%20Refresh.JPG?dl=0

 

There is no option to change the database credentials or anything similar. It used to be there... 

 

We have many reports based on that data set, so renaming the dataset and republishing it is not an option.

 

Thanks!

12 REPLIES 12

@Anonymous Are you connecting to any other data source? Do you see the most recent data when you look at the report from with the Desktop? If there are data changes, have you republished the PBIX to see if that would re-initiate a refresh?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

@Seth_C_Bauer there are no other data sources, everything comes from a single database. From the desktop tool I am able to refresh the data and when I publish it to the service, the new data is available in the service. That's the current workflow, but I would like to be able to refresh the dataset from the service.

 

I tried republishing with different credentials of the data source, but nothing helped. 

 

In the refresh history, I see entries for Scheduled and all rows status is set to completed. The start and end time have the same values. 

Hi @Anonymous,

 

Though you said you didn't use DirectQuery to the Azure SQL database, from the image you shared indicates it's DriectQuery mode rather than Import. The reason is Databases in the cloud.

 

Generally, if the dataset only contain Azure SQL database without any other data sources, we needn't to configure gateway for the dataset. But from the image, it said the "data sources under the data gateway are offline". I guess you have configured Azure SQL data source under data gateway. Please go to Manage Gateways, find the Azure SQL data source, make sure Connection Successful and the gateway host this data source is Online.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-qiuyu-msft

 

This has been a problem for a while, so I've tried all obvious solutions. I never set this up as a gateway, but actually used SQL Server database as a source. Now, I see they added Azure SQL, but back then, they did not had that option. Could that be the reason?

 

When I go to Manage Gateways, it says "You don't have any data gateways."

 

At the beginning (6 months ago), the desktop file was initially pointing to the Azure SQL database (with the SQL Server database source option) and the refreshing was working under the PBI service.

 

Thanks for your help.

Hi @Anonymous,

 

I notice that you hide the information that who configured this dataset. Please let this author check his data gateway and data source, make sure all are online.

 

q2.PNG

 

If the author is yourself, as you don't have any gateway, I guess if some one share the data source under his data gateway with you, you can ask your workmates to check this. See: Manage users.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-qiuyu-msft

 

Also, I think once I have published the dataset from the Power BI desktop with a local database connection. Now, in the Developer Console, I'm looking at the network traffic and this URL: https://wabi-north-europe-redirect.analysis.windows.net/metadata/datamovement/models/1452244/dataSou...

 

And I see collection called "monikers" and each item has a property called "monikerDataSources" which is a collection of objects. All of the objects are pointing to my local database called ".\sqlexpress2016". I suspect that this is the problem.

 

How can I clear these data sources and make the data set point our production DB?

Anonymous
Not applicable

@Anonymous 

Check the Azure SQL side... Did the firewall rules change, access permissions on that side, did the password of the user change?

 

 

I could throw down a bunch of hyjinks that you could try in the Desktop, but I don't necessarily know what the cause of this could be...

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

Thanks @Seth_C_Bauer

 

The "Allow access to Azure services" is set to ON. Is there some other firewall setting on the Azure SQL?

 

The problem is that I can't even change the credentials to the DB from the PBI service. Can I do this from the Desktop tool?

Hi @Anonymous,

 

Regarding this issue, I would suggest you create a support ticket to get dedicated support from Microsoft engineer.

 

Support Ticket.gif

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Anonymous Yes. This may require you to clear your datasource cache in the Desktop if you need to change those creds, but you should be able to do that without issue.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

@v-qiuyu-msft

 

I set up the dataset. We are only 2 persons in the workgroup. 

 

We tried swithcing over the ownership, but without result. The other person also doesn't have the gateway.

 

Not sure what else to try... Is there anyway I can contact Power BI team directly for this issue? 

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