Admin API from Azure Data Factory

[JasonUWA]

I am trying to collect data from the Power BI admin API from Azure Data Factory (ADF). I have successfully used this API from powershell, but I am required to do the task in ADF (without using azure function app).

I am following this guide: https://docs.microsoft.com/en-us/azure/data-factory/connector-rest. In short, I'm using a copy task to GET from the API and save the data to a storage account.

1. I have set up a linked service (REST) pointing to https://api.powerbi.com, using the same account I have been using in Powershell (basic authentication using user and password). Testing the connection is successful.

2. I have set up a dataset (REST) that uses the linked service, with the Relative URL of v1.0/myorg/admin/reports (thus pointing to https://api.powerbi.com/v1.0/myorg/admin/reports)

3. I have set up a copy task with the source as the REST dataset, request method GET, and no body. The sink is a storage account. When I preview the source data or debug the pipeline, I get the following error: 

{ "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=UserErrorHttpStatusCodeIndicatingFailure,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The HttpStatusCode 403 indicates failure. ,Source=Microsoft.DataTransfer.ClientLibrary,'", "failureType": "UserError", "target": "Copy data from REST to ADLS", "details": [] }

I am confused as to why I am getting a 403 error, while the same account in powershell is able to authenticate and get data from the API. What am I doing wrong?

Has anyone had experience doing this in ADF? Got any advice?

Cheers,

Jason

[JasonUWA · Accepted solution]

I solved my own problem, all it took was another day of poking around in the dark.

In my mind, the linked service was doing the authentication and token passing for me, but that is not the case.

For anyone else who finds this and is having the same issue, here is what needs to be done to set up PowerBI admin API in ADF (mostly based on this: https://docs.microsoft.com/en-us/azure/data-factory/connector-rest😞

1. Create a REST linked service using the base URL "https://api.powerbi.com" (no quotes) and authentication type Anonymous.

2. Create a REST dataset pointing to the above linked service, with the relative URL something like: "v1.0/myorg/admin/dashboards" (no quotes).

3. Create a pipeline, and bring in the Web activity to get a token. Use the URL "https://login.microsoftonline.com/<Tenant ID>/oauth2/token" (no quotes), POST method, add a new Header with the name of "Content-Type" (no quotes) and Value of "application/x-www-form-urlencoded" (no quotes). The Body should be:

grant_type=password&username=<username>&password=<password>&client_id=<AppID>&client_secret=<AppKey>&resource=https://analysis.windows.net/powerbi/api

Where <username> and <password> belong to a user account that has the Power BI Service Administrator role in your tenancy, and <AppID> and <AppKey> belong to the service principal set up using guidelines here: https://docs.microsoft.com/en-us/power-bi/developer/register-app.

4. Connect this activity to a new Copy data activity. The source dataset should be the one you created before, request method of GET. The Sink dataset can be what ever you like (in my case, sending it to ADLS2)

The trick was knowing that you needed to authenticate with a user account because service principals can't use the admin API.

However, you still need one for Oauth2 authentication. 

Hopefully this helps someone else out there, so you don't have to spend as much time hitting your head against a wall as I did!!!