topic Re: Update Datasource and Refresh using Service Principal in Developer

Update Datasource and Refresh using Service Principal

JAnder — Fri, 06 May 2022 15:09:27 GMT

I am working in a multi-tenancy setup where each tenant has their own database. I am uploading a pbix file to a workspace and then trying to update the datasource to the correct connection and refresh the data using the API.

I can update the datasource fine by calling client.Datasets.UpdateDatasourcesAsync on the PowerBIClient. I pass in the UpdateDataSourcesRequest which contains a DatasourceConnectionDetails object, inside which is the server and database details. This succeeds however when I then call client.Datasets.RefreshDatasetAsync to refresh the data, I get an 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.

The issue appears to be the dataset is "owned" by the service principal. If I login and take over it, then refresh, it works. My aim is to be able to upload these pbix files to tenants and update to the correct datasource using the principal. Is this possible or will I have to use a master user for this instead?

Thanks

Re: Update Datasource and Refresh using Service Principal

Anonymous — Mon, 09 May 2022 09:38:46 GMT

Hi @JAnder ,

 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.

According to your statement, please make sure you have configured credentials of your data source correctly in Power BI Service. If your data source need gateway to refresh, please add the credential to data source in gateway.

Configure scheduled refresh

Add or remove a gateway data source

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Update Datasource and Refresh using Service Principal

JAnder — Mon, 09 May 2022 16:04:12 GMT

Hi @Anonymous

Thanks for responding. This is all being done via the API so logging in and doing it using the Power BI portal is not really an option. The data source already exists and is in use elsewhere though, so I am confident the credentials are OK.

The problem seems to be because I am uploading a pbix and switching the datasource connection string using the API, which uses the Service Principal. The service principal takes ownership and then cannot refresh. If I was to login and "take over" the dataset with an admin account it would refresh ok.

I am looking for ideas how to handle that with the API. The answer may just be I cant use the principal and need a master user?

Re: Update Datasource and Refresh using Service Principal

bhushant — Thu, 07 Jul 2022 06:52:05 GMT

Hi @JAnder and @Anonymous -

I am trying to solve similar problem. please let me know if you have found a workaround or solution

Please see my use-case below

Use Service Principal to connect Data Source [ Azure SQL DB , Azure Databricks Delta Lake]

When the PBIX is published to wokspace, using REST API - Takeover is performed and ownership is transfered to Service Principal [with admin access to workspace] [Ref: https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/take-over-in-group]
After Takeover, the Data Source credentials are required to be refreshed as well, as we are using Service Principal we have first obtain a Token and then using REST API authenticate Credentials - https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource#oauth-2.0-credentials-example
After this Step - Embedded Report starts working and connection to Dat Source is sucessfull , but after the Token expiry the Report stops working

Please can you let me know

if these are the right steps to Authenticate / Update Credentials of Service Principal ?
Any other workaround to use Service Principal based Authentication to Data Source from PBI-Embedded

Appreciate your help, Many Thanks

Re: Update Datasource and Refresh using Service Principal

vamshikrishna20 — Sat, 24 Jun 2023 16:33:14 GMT

Hi @bhushant and @JAnder , I have a similar use case to refresh datasets using SPN. please help me know if this got resolved

Re: Update Datasource and Refresh using Service Principal

bhushant — Mon, 26 Jun 2023 04:55:55 GMT

Hi @vamshikrishna20 ,

Using "Service Principal to Authenticate Data Source Credentials" , is still not supported. Power BI is unable to get RefreshToken for Service Principal after initial Authentication during Data Source Credentials update. As a work-around, I have implemented following

Embedd Report using Service Principal or Master Account
Update Data Source Credentials using Service Principal or Master Account
While Updating Data Source Credentials with OAUTH2.0 set this [useEndUserOAuth2Credentials] to True,this will ensure EndUser AD credentials are used for Authenticating DataSource
- ref- Gateways - Update Datasource - REST API (Power BI Power BI REST APIs) | Microsoft Learn
If the above param is not set to true, Service Principal or Master Account credentials/token is stored in connection, this token will expire after few hrs based on Service Principal or Master Account AD config
To render / load PBI report in Application using iframe, I am using a Master Account as ServicePrincipals can not be assigned PBI licenses

Hope this will help you, I think with MS Fabric there are changes expected in the REST API and AUTH process

Thanks

Re: Update Datasource and Refresh using Service Principal

vamshikrishna20 — Mon, 26 Jun 2023 11:29:19 GMT

@bhushant , will this option also works for Direct Query also ?

Re: Update Datasource and Refresh using Service Principal

bhushant — Mon, 26 Jun 2023 14:15:31 GMT

@vamshikrishna20 - Yes, this will work for Direct Query as well , I have used this method for Direct Query connection to Azure Databricks Unity Catallog tables, Azure SQL Server Tables , ADLS Gen2 files

Re: Update Datasource and Refresh using Service Principal

vamshikrishna20 — Tue, 27 Jun 2023 08:40:03 GMT

@bhushant , sorry if my questions seems lame, just wanted to recheck- if you have implemented this using spn or master account.

if SPN, my assumtpion is this will work through access token which gonna expire in one hour. so after one hour will the report refresh fail

Re: Update Datasource and Refresh using Service Principal

bhushant — Tue, 27 Jun 2023 08:51:06 GMT

@vamshikrishna20 -I have implemented this using Master-Account [AzureAD user],where the master account user will have permissions on to query data on data sources.

Thanks

Re: Update Datasource and Refresh using Service Principal

vamshikrishna20 — Tue, 27 Jun 2023 12:40:31 GMT

@bhushant , do we have any documentation from microsoft or what was the turning point for you to switch from spn authentication to master account authentication.

Also, to run the CD pipeline with Master account, do we have to set any permission in Azure Portal?