Use a Service Principal with a Certificate as Credentials for a Datasource

[Graham-TA]

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?

[lbendlin]

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.

[Graham-TA]

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#permis...). 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.