Changing datasource credentials (OAuth2) for Paginated reports through REST API

[Zencheg]

Hi Folks,

I am trying to set OAuth2 credentials for the Paginated reports (RDL) via PowerShell using service account (not SPN) with username/password, but getting 401 response from API:

{
  "code": "PowerBINotAuthorizedException",
  "pbi.error": {
    "code": "PowerBINotAuthorizedException",
    "parameters": {},
    "details": [],
    "exceptionCulprit": 1
  }
}
Response status code does not indicate success: 401 (Unauthorized).

The API I am using is the following:

gateways/$gatewayId/datasources/$datasourceId

Source.

Datasources are Azure SQLs and I am not using gateways at all to access the data, but as I understood, I still need to use some gateways in the query: example

The same REST API I can call successfully using the same account, when I am changing credentials for Datasources on any of the Datasets and it works. 

Patch body is the following:

        "credentialDetails" = @{
            "credentials"          = "{""credentialData"":[{""name"":""accessToken"",""value"":""$Token""}]}"
            "credentialType"       = "OAuth2"
            "encryptedConnection"  = "NotEncrypted"
            "encryptionAlgorithm"  = "None"
            "privacyLevel"         = "Organizational"
        }

The scopes are the following for the account:

Scopes
https://analysis.windows.net/powerbi/api/App.Read.All https://analysis.windows.net/powerbi/api/Capacity.Read.All
https://analysis.windows.net/powerbi/api/Capacity.ReadWrite.All https://analysis.windows.net/powerbi/api/Content.Create
https://analysis.windows.net/powerbi/api/Dashboard.Read.All https://analysis.windows.net/powerbi/api/Dashboard.ReadWrite.All
https://analysis.windows.net/powerbi/api/Dataflow.Read.All https://analysis.windows.net/powerbi/api/Dataflow.ReadWrite.All
https://analysis.windows.net/powerbi/api/Dataset.Read.All https://analysis.windows.net/powerbi/api/Dataset.ReadWrite.All
https://analysis.windows.net/powerbi/api/Gateway.Read.All https://analysis.windows.net/powerbi/api/Gateway.ReadWrite.All
https://analysis.windows.net/powerbi/api/Pipeline.Deploy https://analysis.windows.net/powerbi/api/Pipeline.Read.All
https://analysis.windows.net/powerbi/api/Pipeline.ReadWrite.All https://analysis.windows.net/powerbi/api/Report.Read.All
https://analysis.windows.net/powerbi/api/Report.ReadWrite.All https://analysis.windows.net/powerbi/api/StorageAccount.Read.All
https://analysis.windows.net/powerbi/api/StorageAccount.ReadWrite.All https://analysis.windows.net/powerbi/api/Tenant.Read.All
https://analysis.windows.net/powerbi/api/Tenant.ReadWrite.All https://analysis.windows.net/powerbi/api/UserState.ReadWrite.All
https://analysis.windows.net/powerbi/api/Workspace.Read.All https://analysis.windows.net/powerbi/api/Workspace.ReadWrite.All
https://analysis.windows.net/powerbi/api/.default 

Am I using the wrong URL to patch paginated reports or this is related to permission issue?

Thank you in advance!

[Zencheg]

Hi Xiaoxin Sheng,

Looking at the API, you mentioned, it seems there is no options to provide credentials section.

It is accepting only "updateDetails" in the requestbody.

And within "updateDetails" only connectionDetails and datasourceName can be provided (here).  

When I tried to push credentials section - it replies back with 500 (Internal server error).

Do you any working example of the post body in this case? 

Regarding datasource type - no, I am not changing the datasource type, it stay intact, only trying to update credentials.

Another thing I noticed, on the Gateway API - there are some notes regarding Paginated reports (for example, Permissions section), I am a bit confused now, if it is applicable to rdl reports or not. 

Kind regards,

Zencheg

[v-shex-msft · Accepted solution]

Hi @Zencheg,

Perhpaps you can try to add the authentications in 'updateDetails' similar as following code: 

    "updateDetails": [
        {
            "datasourceName": "{datasource_name}",
            "connectionDetails": {
                "server": "{server_name}",
                "database": "{database_name}",
                "authentication": {
                    "type": "OAuth2",
                    "token": "{oauth2_token}"
                }
            }
        }
    ]

>>Datasources are Azure SQLs and I am not using gateways at all to access the data, but as I understood, I still need to use some gateways in the query

That API only work for the datasource which bind to the gateway. When you are handle with one query mixed multiple type of data sources, you can turn on the option to also use gateway to manage data source which not required gateway. (e.g. a dataset include both on premise and cloud data sources)

Merge or append on-premises and cloud data sources - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

[Zencheg]

Hi, Xiaoxin Sheng!

Thank you for your help. The way you mentioned it is able to set the credentials properly. 

Kind regards,

Zencheg

[v-shex-msft]

Hi @Zencheg,

I'm not so sure if the gateway API fully compatibility with paginated reports.
In my opinion, I'd like to suggest change to use 'Reports - Update Data sources In Group' API to do update data source operations, this API is worked for update specific paginated report data source:

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

BTW, did you change the data source type in these operations? Current power bi rest api not support ot directly change data source types in the update datasource operations.

Limitations
1. Only supports paginated reports.
2. Changing the data source type isn't supported.

Regards,

Xiaoxin Sheng