Consume OC API from Excel/PowerBI with AzureAD Authentication

[jpiquot]

Hi,

I have ODATA APIs in my OC application that work well with Excel/PowerBI when authentication/authorization are disabled.

Now I would like to be able to authenticate and authorize users using they Work/School Account (AzureAD).

I have activated AzureAD accounts in OC. This works well with OC login page.

How can I authenticate the user who's querying data on the OC ODATA service from his Excel spreadsheet or from PowerBI?

I use ODATA, but we would have the same question for GraphQL.

[sebastienros]

/cc @MichaelPetrinolis @PinpointTownes if you have some advice

[MichaelPetrinolis]

Hi @jpiquot .
It seems that PowerQuery does not support custom token providers. (https://stackoverflow.com/questions/52781269/can-excel-powerquery-authenticate-using-identityserver4)
In order to retrieve data I suggest to enable open id server, enable the Client Credentials flow (also setup an application) and modify the following example https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/ to get a token from connect/token endpoint of your OC instance. Then set the bearer token before you call your API.
You could also setup your API to authenticate against the AzureAD, in order to work OOB with organizational accounts.
Maybe @PinpointTownes has more thoughts on the matter :)

[jpiquot]

@MichaelPetrinolis Thank you. I will try these solutions.