Power BI dataset refresh: use service principal to refresh Sharepoint data with access token

[naelske_cronos]

Hello,

I'm trying to refresh a Power BI dataset that has a SharePoint list as datasource, this with the Power BI REST API's via PowerShell and the Power BI modules.

This is the video that I'm taking inspiration from: https://youtu.be/VoZoumwaZsY?t=3216

In Postman I managed to have access to my SharePoint site and list with my service principal and access token this by using MS Graph instead of SharePoint.

Connect-PowerBIServiceAccount -ServicePrincipal -Credential $Credential -Tenant $Tenant_ID | Out-Null

$AccessToken = (Get-AzAccessToken -ResourceUrl "https://graph.microsoft.com/").Token
$AccessToken

$GroupID = '60f50...'
$DatasetID = '6a989...'
$DatasourceID = 'd5dcf...'
$GatewayID = 'ae770...'
$DatasourceURL = "/gateways/" + $GatewayID + "/datasources/" + $DatasourceID

$Body = @"
    {
        "credentialDetails": {
            "credentialType": "OAuth2",
            "credentials": "{\"credentialData\":[{\"name\":\"accessToken\", \"value\":\"$($AccessToken)\"}]}",
            "encryptedConnection": "Encrypted",
            "encryptionAlgorithm": "None",
            "privacyLevel": "Organizational",
            "useEndUserOAuth2Credentials": "True"
        }
    }
"@

$GetAPIResult = Invoke-API -URL $DatasourceURL -Method 'Patch' -Body $Body -ContentType 'application/json'
Write-Output "The credentials for the datasource $DatasourceID have been updated successfully"

But I'm getting a strange error when refreshing my credentials:

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

Someone who can help me with this?

Kind regards

[soniya-01]

Problem: Refresh a Power BI dataset with SharePoint data using a service principal and access token.

Solution:

1. Register App in Azure AD:

   • Create a Power BI app in Azure AD.

2. Get IDs and Keys:

   • Get App ID, Directory ID, and create a Secret Key.

3. Permissions:

   • Grant app permissions to access SharePoint.

4. Power BI Settings:

   • In Power BI, enter app details for service principal.

5. Access Token Magic:

   • Power BI uses service principal info to get an access token.

6. Automatic Refresh:

   • Set up automatic refresh in Power BI service.

[naelske_cronos]

Hello,

I already have an app with everything working but it is not the intention to do this via the UI or Power BI service but programmatically via PowerShell.

Kind regards

[naelske_cronos]

Update,

I managed to refresh my dataset by using the SharePoint API where the service principal has access to my SharePoint environment by having the correct permissions. The only problem here is that to receive an access token, it needs to work with a client certificate but that seems a lot more work that using a client secret which cannot be used in this case.

That's is why my question if someone already used the Graph API instead where a client secret could be instead.

Kind regards

[v-shex-msft]

Hi @naelske_cronos,

The Microsoft Graph API and the Power BI REST API are separate services, and they require different authentication methods and tokens. So you cannot to use the Microsoft Graph API to get an access token for the Power BI REST API. 

According to your description and snapshot, it seems like you are try to use service principal credentials to invoke gateway update data source rest api. 

Did you correctly dispathc permissions that api required? As the document mention, it required Dataset.ReadWrite.All but in you snapshot it only assign the workspace permissions.

Gateways - Update Datasource - REST API (Power BI Power BI REST APIs) | Microsoft Learn

BTW, what type of report and data source are you worked? AFAIK, some paginated report does not include the data sources to binding with gateway, so they can not use gateway update rest api.

For this scenario, you may need to use the paginated report specific rest api to update.

Reports - Update Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Regards,

Xiaoxin Sheng

[naelske_cronos]

Hello @v-shex-msft,

I don't know if you read the whole story that I wrote but it is not that I'm trying to use an access token from MS Graph to call a Power BI REST API. I know that they are two seperate things. In my Power BI dataset I have a SharePoint List as datasource and I would indeed like to refresh this dataset with the following API: https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource based on an access token or with my service principal as you prefer. Because my service principal has full control of my SharePoint environment (for testing purposes) I can read all of its data and with 'Get-AzAccessToken' I'm fetching the access token with my scopes from SharePoint so that I can refresh my dataset based on an access token.

I already managed to refresh a dataset with the SharePoint service 'https://microsoft.sharepoint.com' but now with MS Graph which also has access to the whole SharePoint environment I would like to do it with this one. With the SharePoint service, you need to create a certificate as with Graph you don't but I don't manage to have any access with the Graph service 'https://graph.microsoft.com/'

I'm doing the same thing as here: https://youtu.be/VoZoumwaZsY?t=3216 but with Graph instead.

PS: after a lot of testing with Power BI API's, it is not necessary to add the delegated scopes 'Dataset.ReadWrite.All'. If your service principal is admin/member of a workspace, it automatically gets the required permissions fetch and change its metadata.

Kind regards

[naelske_cronos]

Hello,

My apologies, I made a mistake in my code by adding the parameter 'useEndUserOAuth2Credentials' but data sources as SharePoint don't use DirectQuery which is discussed in the documentation:

$Body = @"
    {
        "credentialDetails": {
            "credentialType": "OAuth2",
            "credentials": "{\"credentialData\":[{\"name\":\"accessToken\", \"value\":\"$($AccessToken)\"}]}",
            "encryptedConnection": "Encrypted",
            "encryptionAlgorithm": "None",
            "privacyLevel": "Organizational"
        }
    }
"@

This is the error that I'm getting now, so it seems that I don't have enough access with my service principal.

{"error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","pbi.error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The credentials provided for the SharePoint source are invalid. (Source at https://bla.sharepoint.com/sites/PowerBI-CICD.)"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceKind","detail":{"type":1,"value":"SharePoint"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourcePath","detail":{"type":1,"value":"https://bla.sharepoint.com/sites/PowerBI-CICD"}},{"code":"Microsoft.Data.Mashup.CredentialError.Reason","detail":{"type":1,"value":"AccessUnauthorized"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"SharePoint\",\"path\":\"https://bla.sharepoint.com/sites/PowerBI-CICD\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"AccessUnauthorized"}}],"exceptionCulprit":1}}}

My service principal has following permissions in MS Entra ID, which are application permissions without user-interactivity. I'm receiving an access token in Postman and I can use the API's the read from lists and documents so it works but it seems that I don't have the access when doing it in Power BI

I'm trying to do the same thing as you did @TedPattison with your video so maybe you can give me some pointers. I'm trying to do it with MS Graph instead of the SharePoint resource URL.

Kind regards