topic Re: Updating OAuth2 Dataset credentials via REST API in Developer

Updating OAuth2 Dataset credentials via REST API

Anonymous — Fri, 16 Jul 2021 12:13:48 GMT

Hi All,

I'm running into a issue which is keeping me busy for quite a while now. My goal is to update datasource credentials via PowerShell. I'm now trying it via the REST API Tryout pane from MS. My Powershell Code runs into the exact same issue.

Scenario:
Dataset with two connections: 1 connection to SharePoint, 1 connection to SQL.
Updating the SQL connections works fine, I'm using the basic request body as described here.

Performing the same update on the SharePoint connection with the OAUth2 body fails. Below the body i'm using (obviously with another accesstoken value.

I've tried several methods of retrieving the access token, and I have no idea what way of retrieving it is correct...
Method 1: Retrieving it via Get-PowerBIAccesToken
Method 2: Via my registered App in SharePoint with FullControl permission on the corresponding site.

Error response is. Any help would be very much appreciated! 🙂

Re: Updating OAuth2 Dataset credentials via REST API

Anonymous — Mon, 19 Jul 2021 09:49:19 GMT

Hi @Anonymous

Your request is from this blog OAuth2 credentials example, it should be correct.

Rest API:

PATCH https://api.powerbi.com/v1.0/myorg/gateways/{gatewayId}/datasources/{datasourceId}

Body:

{ "credentialDetails": { "credentialType": "OAuth2", "credentials": "{\"credentialData\":[{\"name\":\"accessToken\", \"value\":\"eyJ0....fwtQ\"}]}", "encryptedConnection": "Encrypted", "encryptionAlgorithm": "None", "privacyLevel": "None" } }

Try steps as below to get the correct sharepoint access token.

For reference:

https://stackoverflow.com/questions/63321532/sharepoint-rest-api-how-to-get-access-token

https://anexinet.com/blog/getting-an-access-token-for-sharepoint-online/

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Updating OAuth2 Dataset credentials via REST API

Anonymous — Mon, 19 Jul 2021 18:22:22 GMT

@RicoS Thanks for your reply.
I've tried both scenario's (appregistration on SP and the App Registration in Azure AD). Both situations give me an access token. Both access tokens are not accepted by the request I'm sending to the endpoint below.

PATCH https://api.powerbi.com/v1.0/myorg/gateways/{gatewayId}/datasources/{datasourceId}

Is it possible for you to give it a try yourself? I'd really like to know if this is just me not being able to do this, or that the API is just not working properly.

Re: Updating OAuth2 Dataset credentials via REST API

Anonymous — Fri, 23 Jul 2021 06:37:00 GMT

Ok I figured this one out. It seems requesting an access token by 'grant_type' = 'password' does the job, while requesting it via 'grant_type' = 'client_credentials' doesn't.

Below the first scenario that works, the response includes a refresh token.
The second scenario does not work.

Re: Updating OAuth2 Dataset credentials via REST API

Anonymous — Wed, 30 Mar 2022 20:20:47 GMT

Hi Remy!

I'm having the same problem as you had, and still can'tseem to get a valid token to update the Sharepoint credential...

Could you go through all the steps you took to make it work? (Service Principal configuration and api permissions, any Sharepoint permission granting)... I'm literally running out of options to test this.

Thank you in advance.