Power BI SharePoint Connection Authentication that allows users to refresh data

[Rob92]

I have a Power BI report that contains a Power App.  The Power App allows users to input values, and writes those inputs back to a SharePoint list.  Under the app I have a Power Automate button that refreshes the dataset (containing data gateway connections to SQL server & MongoDB, as well as the cloud connection to Sharepoint).  I had the Sharepoint connection setup in PBI service using OAuth 2.0 as the authentication method.  With this setup, scheduled refresh works, and it works when I trigger the refresh button in the report.  However it does not work when other users hit the refresh button in the report.  I assume this is because the OAuth I've Authentication for the Sharepoint connection is set up with my credentials, so it doesn't work when other users trigger it.  I have tried setting up Service Principle authentication but not getting any luck and that seems like it may have to do with an unresolved microsoft bug, although I'm not 100% about that either.  Any ideas how get this working?

[christinepayton · Accepted solution]

They need permission to the workspace and the gateway connections in order to refresh it too, do they have that? They also may need run-only permission on the flow to run the flow itself, which you have to explicitly set in the flow properties window. I would have a user try running the dataset refresh from the workspace with the refresh button there and see if that works - that will tell you if it's Power BI permissions or flow permissions. I don't think it's SharePoint permissions in this case, I would try the above first. 

[Rob92]

Thanks Christine.  I gave the users permissions run permissions on the flow, contributor access to the workspace, and added as users on the data connections.  After doing this it works.  

The reason I didn't try that initially (which I'm still confused about) is that I have another report with several buttons that trigger flows that refresh other Power BI datasets - the same MongoDB & SQL Server gateway connections as is used in the flow this post is about.  The only difference is that this one has a SharePoint connection.  I'm still not sure how end users were able to trigger those other refreshes without adding all those permissions.

[christinepayton]

I think it depends on what kind of connection setup you have. It's going to run refreshes under the permissions of the dataset owner usually, so anyone with workspace access can refresh, but iirc if you have SSO conigured it'll use the current user permission (though usually that's a direct query thing, so wouldn't necessarily apply here). The button-click flows to my knowledge always require the run-only user permissions as a security measure. Technically that button click is just triggering an API call, so if anyone could trigger the API call using the permissions of the flow owner as many times as they wanted, it'd be a bit of a security issue (depending on what the flow is doing - since you can have flows do things other than refresh datasets).