Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
naelske_cronos
Helper III
Helper III

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

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.
Capture.PNG
 
 

 

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
6 REPLIES 6
soniya-01
New Member

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.

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
Helper III
Helper III

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
Helper III
Helper III

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:

naelske_cronos_0-1701428220039.png

 

$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

Capture.PNG

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.