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

[nilanjanpalMol]

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]

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.

Why would they restrict manual access to the workspaces for workspace administrators or developers?

[nilanjanpalMol]

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]

HI @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 

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]

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);

I am finding below error:

"{\"error\":{\"code\":\"InvalidRequest\",\"message\":\"Property useEndUserOAuth2Credentials is only supported with credentials of type OAuth2 and datasource type of Web\"}}"

I have checked datasource type is showing as "Web" and Credential type is "OAuth2"

[Anonymous]

HI @nilanjanpalMol,
>>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. 

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 

[nilanjanpalMol]

Dear Xiaoxin Sheng,

Thank you very much for your help.

I have successfully fetched the datasource details.

Now I want to set credential using Power BI Rest API. I am using below code as I mentioned in my previous comment.

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);

I am getting below error:

"{\"error\":{\"code\":\"InvalidRequest\",\"message\":\"Property useEndUserOAuth2Credentials is only supported with credentials of type OAuth2 and datasource type of Web\"}}"

I am not very sure, if “Anonymous” authentication will be used or not. As you suggested, I tried to use anonymous authentication from Power BI desktop. It was not worked.  

Also it will be helpful, if you suggest any link where I will be able to get any example of OAuth authentication for setting dataset credential using Power BI Rest API.

I have already implemented "Basic" authentication (using Power BI Api) for setting credential for database based Power BI report. For OAuth authentication I am getting problem.

With regards

Nilanjan

[Anonymous]

HI @nilanjanpalMol,

Have you tried to update on the power bi service side to confirm these credentials are valid for your datasets?
If your request worked to use 'base' authorization and the OAuth mode work on power bi service, it may mean the OAuth data credentials are currently not able directly configurated by API.
Regards,

Xiaoxin Sheng