Forum Discussion
Use a Service Principal with a Certificate as Credentials for a Datasource
I'm working on a project to automate this process via API calls:
- Import a PBIX file including a Dataset with one datasource, which is linked to an Excel file in SharePoint
- Change the Datasource to a different Excel file in SharePoint (with the same structure)
- Clone a report and link to the Dataset
- Set a refresh schedule
The issue I'm facing is that once the PBIX file is imported, it cannot be sucessfully refreshed, either with the original datascource or the updated one. If I use the web UI I can edit the credentials and then the refresh will be successful, but this doesn't help with automating the process.
I considered using the API to update the gateway credentials, using an OAuth2 token, however I read that the API does not handle refresh tokens, so this is only good for one hour. The other approch is to use a Service Principal for the credentials, however I can only see options to use a client (app) id with client secret. Using a client secret doesn't work with SharePoint, it seems SharePoint will not authentiate a Service Pricipate with a client secret, it needs to use the certificate method.
Is there any way to use a certificate to authenticate the Service Principal in a gateway?
2 Replies
- lbendlin
Super User
Even if all that would work it still doesn't solve the "take over dataset" part in the service that is required before you can set the refresh schedule.
My understanding is that the user authenticated for the API call must be the owner of the dataset to set a refresh schedule (https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-refresh-schedule-in-group#permissions). Also that the user authenticated in the call to update the gateway must be the owner of the dataset (https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource#permissions). Have I misunderstood something there?
To be clear, I am not using a service principal to call the API, only to retrieve the Excel file from SharePoint. I would happily not use a service principal at all if we could set the refresh token via the API.
