Data source credentials and scheduled refresh greyed out

[Anonymous]

HI folks,

I've seen many posts on this subject but none I've been able to apply.

I have a DB in Azure and connect to the server/db thru PBI desktop. I am able to refresh the report visuals through the Refresh option in PBI Desktop. All good. Then when done, I publish to the PBI Service. All good. This is where it all ends!

I've even used Power Automate to setup a flow (as a trial) to refresh one of my datasets. From the Flow side the refresh appears to work. ie. it completes successfully. However when I go to the PBI Service, select the relevant test dataset, then settings, it states that the refresh failed... under show details .... it states:

Data source error:

It looks like scheduled refresh failed because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources.

However this message IS in the Settings page and both the Data source credentials AND the Scheduled refresh options are greyed out ... sorry, but I'm @ a loss as the PBI desktop connects successfully to the Az Db using the server/user/pwd credentials. I understand a Gateway is not req'd as the DB is an Az SQL DB ... TIA .... but I can't see a way past this .... everything is in the cloud, apart from running the PBI on my desktop, which is bbviously a standard process. Rgds

[vodka_ATX]

Hello Power Peeps,

I am troubleshooting this issue. In my case I think the problem is due to an Excel file on a network share. The data source is listed as X:\filepath\filename.xlsx but should be \\servername\sharename\filepath\filename.xlsx

I think the solution is to open the PBIX file in Power BI Desktop and edit the datasource connection, save and republish.

Note: I tried the "take over" between two different accounts, mine and an admin account. That didn't fix the issue. That's when I noticed the path to the Excel file was listed as a mapped drive instead of the UNC path.

[john-v1]

Mine was greyed out too, and funny enough what fixed it was letting another admin in the workspace take over control of the dataset, and then I took back control.

It is important to note that when he took over control the first time, a lot of stuff was greyed out for him (including Gateway and Cloud Connections, Parameters, and few others).

Only when I took back control from him, did everything start to work for me.

[amitchandak]

@Anonymous , I saw one more use complain same. First, check it is not asking for a gateway. If so , why it is, check do you have any local files used.

Check Know issues -https://docs.microsoft.com/en-us/power-bi/troubleshoot/known-issues/power-bi-known-issues

Log an issue, if needed : https://community.powerbi.com/t5/Issues/idb-p/Issues

[Anonymous]

Hi this actually solved my problem. I have both web and local files used in the data model. I can't schedule auto fresh and my data source credentials is grayed out. Google brought me here for my trouble shooting. After removing the local files, the data source credentials becomes normal and scheduled refresh can be configured. I looked it up in ChatGPT. It explained the reasons for this well. Power BI Service doesn't have direct access to files stored locally. Scheduled refresh requires data sources that are accessible over the internet or a network. Even if PBI Servcie could access local files, managing credentials would be cumbersome and insecure. Plus, local files may not always be available or accessible, if you computer is turned off, disconnected from the network, or if the path is changed. In short, scheduled refresh requires data sources that are consistently available to ensure the reliability and accuracy of your reports and dashboards. 

Thank you for your insights! Something new I learned today. 

[sboonefsu]

When you talk about storing files locally, I am a bit confused. I utilize powerbi for files that are stored in my onedrive. does this qualify as "stored locally"? 

[Anonymous]

Thank you! The local file was the issue! 

[Anonymous]

Hey Amit,

Thanks for your feedback. UNsure exeactly where to check if the process is asking for a gateway. I presume it would be noted in the error msg. I do not see any reference to a g/w in the error msg.

No local files ONLY access to Az DB tables, so in theory, no g/w should be req'd ..... this is something so fundamental that shouldn't be so convoluted ...... PBI is excellent but this is v unimpressive.

rgds

[v-kkf-msft]

Hi @Anonymous ,

If you see option "Use an On-premises or VNet data gateway" enabled on under Gateway connection and it cannot be changed, then the gateway is required for this dataset, like this.

These are some similar threads, please refer to

https://community.powerbi.com/t5/Power-Query/SQL-Azure-Import-Data-Refresh/td-p/88481/page/3 

https://community.powerbi.com/t5/Service/Power-BI-Online-Schedule-refresh-from-Azure-SQL-Server/m-p/1101502 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[donnellh]

Thank you, this was what I needed to be told