Dataset credential is missing while publishing using Rest Api for report based on SharePoint Excel

Question

I am trying to publish a Power BI report. It's underlining dataset is an excel file and that file is in SharePoint.

I am using service principal to authenticate Power BI service.

I am using below code to publish the report.

***************************************************************************

restUrlImportPbix = "https://api.powerbi.com/v1.0/myorg/groups/" + workspace + "/imports?datasetDisplayName=" + importName + "&nameConflict=Overwrite";

var pbixBodyContent = new StreamContent(File.Open(pbixFilePath, FileMode.Open));
// add headers for request bod content
pbixBodyContent.Headers.Add("Content-Type", "application/octet-stream");
pbixBodyContent.Headers.Add("Content-Disposition",
@"form-data; name=""file""; filename=""" + pbixFilePath + @"""");
// load PBIX content into body using multi-part form data
MultipartFormDataContent requestBody = new MultipartFormDataContent(Guid.NewGuid().ToString());
requestBody.Add(pbixBodyContent);
// create and configure HttpClient
HttpClient client = new HttpClient();
client.DefaultRequestHeaders.Add("Accept", "application/json");
client.DefaultRequestHeaders.Add("Authorization", "Bearer " + AccessToken);
// post request
var response = client.PostAsync(restUrlImportPbix, requestBody).Result;

***************************************************************************

Report is published successfully. But, underlining data source is missing the credential. As a result, dataset is not being refreshed unless credential is set manually from Power BI service.

As pre requirement, manually access to the workspace is restricted.

But while uploading a report based on Azure Sql DB, dataset is not losing the credential.

I am searching for a solution, if we can keep the credential during publish report.

lbendlin · Answer

This reads like buzzword bingo - "Excel and Sharepoint and Service Principal". I think you picked a rather very complex scenario here. Please clarify if this is SPO, and if a gateway is involved.
&nbsp;
Why would they restrict manual access to the workspaces for workspace administrators or developers?

nilanjanpalmol · Answer

We have a portal. We are showing Power BI report through that portal (Embedding Power BI report).
As per the requirement, user is developing Power BI reports and publishing the reports through that portal. We are using Power BI rest api for publishing the report. Currently all the reports based on Azure SQL DB. Means user's are connecting Azure SQL DB, designing the report and publishing the same using our portal. So, far it is working properly.
There is no manual intervention. So, access to the Power BI service workspace is not possible.
Recently, we get a new requirement where user will design report based on excel file from SharePoint. During development we stuck of with above problem.
I don't know whether gateway is involved or not.

anonymous · Answer

HI&nbsp;nilanjanpalMol,
Can you please share some more details about your report settings? (e.g. data connector type, query table operation steps, data source credentials that you configured) They should help us clarify your scenarios.
How to Get Your Question Answered Quickly&nbsp;
In addition, you can also take a look at the following link to work with SharePoint files with a web connector, they can be refreshed with 'onedrive refresh' and not required to manage with data gateway.
Use OneDrive for Business links in Power BI Desktop - Power BI | Microsoft Docs
Regards,
Xiaoxin Sheng

nilanjanpalmol · Answer

Thank you very much for your reply.I have created a sample report based on a sample excel data. Excel file is in Microsoft 365 SharePoint. I have followed the step which you mentioned in 2nd link. Data source type is "Web". File path I had taken as it was mentioned in that link.My SharePoint Credential and Power BI Service credential are different.While publishing the report under lining data source is losing credential. Of course, I am able to add credential manually from Power BI service. I have checked that if it is set manually refresh operation is working properly. But I want to retain the old credential as it happened for the report based on database data.Also, It will be ok if I can set OAuth2 credential using Power BI Rest Api. I have tried already using below code:var datasource = await client.Datasets.GetGatewayDatasourcesInGroupAsync(Guid.Parse(workspaceid), report.DatasetId);var delta = new UpdateDatasourceRequest{CredentialDetails = new CredentialDetails{CredentialType = "OAuth2",Credentials= "{\"credentialData\":[{\"name\":\"accessToken\", \"value\":\"" + t.AccessToken + "\"}]}",EncryptedConnection= "Encrypted",EncryptionAlgorithm= "None",PrivacyLevel= "None",UseEndUserOAuth2Credentials = true}};await client.Gateways.UpdateDatasourceAsync(datasource.Value[0].GatewayId, datasource.Value[0].Id, delta);&nbsp;I am finding below error:"{\"error\":{\"code\":\"InvalidRequest\",\"message\":\"Property useEndUserOAuth2Credentials is only supported with credentials of type OAuth2 and datasource type of Web\"}}"&nbsp;I have checked datasource type is showing as "Web" and Credential type is "OAuth2"

anonymous · Answer

HI&nbsp;nilanjanpalMol,&gt;&gt;My SharePoint Credential and Power BI Service credential are different.
For power bi service report dataset settings, it required you use the same credentials used in the power bi desktop.
If you are confused about using rest API with OAuth authorization, you can refer to the following link:
Solved: Pull data from a REST API Authentication - Microsoft Power BI Community
If you are not sure about the data source connections in the rest API, you can try to use rest API 'get datasource' to get the result from a similar type of data source as a template to modify and use in your request.&nbsp;
Datasets - Get Datasources - REST API (Power BI Power BI REST APIs) | Microsoft Docs
Datasets - Get Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs
Notice: web type data source normally used 'anonymous' authorizations, the data source credentials and verifications operations will be attached to the query table steps.Regards,
Xiaoxin Sheng&nbsp;

Forum Discussion

Dataset credential is missing while publishing using Rest Api for report based on SharePoint Excel

7 Replies

Related content

Sharepoint connection credential expiring

Publish Report

credential error sharepoint excel to powerbi

Destruction of credentials

Sharepoint list mirroring