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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JasonUWA
Frequent Visitor

Admin API from Azure Data Factory

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

1 ACCEPTED SOLUTION
JasonUWA
Frequent Visitor

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!!!

View solution in original post

1 REPLY 1
JasonUWA
Frequent Visitor

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!!!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.