Credential Issues with Power BI dashboard refreshes | Best Practices for authentication

[jsande3]

I have been unsuccessful refreshing a datasource using the API or the web application (both throw a datasource credential error). Within the app, the refresh works fine with the data connection as-is. Therefore, I believe my issue is either an API authentication bug or a sync issue between Power BI service and Desktop when the datasource was changed (new data connection didn't update on the web app; see note#1 below).

Set 1 Configurations/Conditions:

• Excel File imported using the SharePoint URL --> Get Data using the Web option

  • Note: Originally it was a local OneDrive Excel file import, but I changed the source to a Web SharePoint import in Power BI Desktop

• Refresh in App on-demand works: Power BI Desktop Dashboard updates from the SharePoint Excel file but does not update Power BI report even after waiting an hour

Set 2 Configurations/Conditions:

• Same import method as above. File imported using the SharePoint URL --> Get Data using the Web option

• Two methods tested here:

  1. Triggered via Power Automate flow

  2. Refreshed triggered manually in web App

• Both methods received the error as below

`Data source error: Scheduled refresh is disabled 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. Then reactivate scheduled refresh`

Note#1: The Datasource in Power BI Desktop is very clearly the SharePoint URL connection (web), and the file location for the Datasource in the web app shows as OneDrive file on a local C:drive. So the Datasource web connection location is not passing through to the web version for some reason. Originally it was a local OneDrive but I updated it, and apparently it did not sync. How can I get the web app to trigger sync that new web location for the datasource? Perhaps this is the problem.

Note#2: Attempts to input or resolve credentials in the Power BI dataset from the web app result in a spinning wheel that never connects.

Note #3: I have an enterprise account with my org, but recently signed up for the developer community. I believe my account in the Power BI service indicates a Trial account now for this reason (superseding the org account).

So why can I authenticate in the desktop app and why is this throwing an error outside of the app? Why doesn't the web app show the same datasource as the desktop app? How do I fix this? 

And finally - are there best practices for setting up datasources with accounts? Should you use individual org accounts for this?

[otravers]

Try to simplify the scope of what you're trying to troubleshoot, which is an authentication issue in the Power BI service against your data source. Meaning: 1) forget about Power BI Desktop refreshes as things can refresh locally but not in the service for a variety of reasons, and 2) forget about Power Automate for the time being. In other words, what you really need to resolve is the following:

"Note#2: Attempts to input or resolve credentials in the Power BI dataset from the web app result in a spinning wheel that never connects."

Now what could be the root cause of that behavior? Based on what you wrote: If the service doesn't show the same data source as Power BI Desktop, it suggests that you didn't republish to the service from Desktop after you changed the source. Can you try that and report where you're at?

[jsande3]

Thank you for the response on this. I do believe you are correct for the syncing here because I republished it and was able to see that the dataset synced to a Sharepoint web URL versus the local C: folder.

I had actually gotten it to work a different way prior to your message. I set up an on-premises data gateway using the utility, which connected the dataset in Power BI cloud to the local data set. This may be just another way to solve the same problem, but my preference is to avoid any local gateways as I imagine that is not best practice. 

I may make another post about best practices for credentials, because I am not sure if using an invidual's organizational credentials is the proper way to Oauth datasets to which there are many connections/automations.

[Daryl-Lynch-Bzy]

Hi @jsande3 - without seeing example of the code, it is difficult to advise.  If you can't provide this detail, one thing you could try moving your Query from Desktop into a Power BI Dataflow.  You simple need to copy and paste it into Power Query from Desktop to Webpage.

In fact, when using SharePoint as a data source, I prefer using Dataflows instead of Power BI Desktop. 

Another thing I recommend is using the SharePoint.Files connector instead of Web.Contents.  I know people attest to it being faster, but it is also more conplex.  SharePoint.Files offers more flexibility to see all files in the SharePoint site, and filter to the required file.  Performance is unlikely to be issue.

[jsande3]

Thank you for the reply and the advice as well. I will certainly look into the Dataflow piece since I have migrated to a SharePoint list instead of an Excel file in OneDrive. Anyway - it appears my issue was a simple oversight in republishing after I changed the datasource so many times. 

To fix the problem a different way, I set up an on-premises data gateway using the utility, which connected the dataset in Power BI cloud to the local data set. This may be just another way to solve the same problem, but my preference is to avoid any local gateways as I imagine that is not best practice. 

I may make another post about best practices for credentials, because I am not sure if using an invidual's organizational credentials is the proper way to Oauth datasets to which there are many connections/automations.