Newbie help with Pipeline copy from Web API that uses header token

[Scott_Powell]

Hi, I'm (completely) new to data pipelines - I come from a Power BI world so I know dataflows not pipelines.

I'm trying to use a COPY activity to copy data from a web API. From PowerShell I'm able to call the web API using code similar to the following. I'd like to use a COPY activity to do the same.

The issue I'm running in to is creating the connector. The API connector only allows you to choose NONE or BASIC for the authentication types. I see in this post Solved: Web API: Headers in Web-Activity vs. Headers in Co... - Microsoft Fabric Community that it's possible to add in header token info after the connection is created. But I'm unable to create the connection in the first place due to authentication failing because I can't pass in the header token in the "create connection" dialog.

Any advice on how to get this to work?

Thanks,

Scott

p.s. the PowerShell script I use is similar to the following:

$uri = 'https://someurl.com/api/nameofapitouse'
$headers = @{
'some_api_token' = 'HARD CODED token value given to our organization to use'
}

$DataFromAPI = Invoke-RestMethod -Uri $uri -Method Get -Headers $headers

[GraceGu]

1st step, get bearer token with a Web Activity.

2nd step, add output from the 1st step into authorization header of copy source connector (source connection is a "dummy" connection when created with anonymous/none authetication type, runtime get authenticated with bearer token header) . 

You can refer to How to pass bearer token API in Azure Data Factory - Stack Overflow. 

[Scott_Powell]

Hi @GraceGu , I had actually seen (and tried) that Stack Overflow post with no success. I'm unable to create a dummy connection using anonymous/none type - when I try, it fails to authenticate and won't let me continue - so the dummy connection never gets created. Screenshot below. 

Since I'm unable to get the dummy connection to save, I'm not able to move forward to the point where I can add the header info. Any suggestions?

Thanks,

Scott

[DavidCousins]

Hey Scott,

for the copy activity you need to use a WEB connector. Give it the base URL and no authentication. Then in the copy activity itself on the source tab, you can define the connection type as REST. Complete the rest of the URL in the Relative URL field and then you open the advanced options and add your Authorization header.

It stumped me for an hour when I first encountered it, it's really not intuitive. The REST connector is only useful in very limited scenarios, for anything needing a bearer token it has to be WEB. Especially frustrating considering all of the MS REST APIs actually need bearers, so you can't use the connector for anything they produce 😄

[Scott_Powell]

hi @DavidCousins , trying to follow your suggestion but not making much progress. I set up a pipeline and add a copy activity, then go to the Sources tab:

When I click to add the new connection, there isn'ta WEB connector offered, only HTTP or REST:

With either of those two options, I don't have the opportunity to enter the header token, and therefore can't get past connector creation.

Am I missing something? I did notice that I can add a WEB activity directly in to the pipeline, and using it I can enter the header info and it seems to return data - but I'm not sure at that point how to "hook it up" to the copy activity so it can be used as a source.

Sorry for the newbie questions, thank you for the assistance!

Scott

[DavidCousins]

I wasnt as clear as I could have been, so no worries! I can't add images sadly, but heres some instructions:

1. In your pipeline create string Variables called bearer_type and bearer_token and set the values as your "some_api_token" and "hard coded" token, respectively
2. In a copy activity for source create a HTTP source against the base URL for your API. In your example it's https://someurl.com/
3. Use Anonymous for the authentication of this connection. If this URL isnt publically pingable then this will fail and you won't be able to do this until On-Prem data gateways are enabled for data factory to use.
4. In the copy activity set the connection type to REST
5. In the Relative URL build whatever string you need. Your example would be:
   1. /api/nameofapitouse
   2. Under advanced set the request method to GET
   3. Create 1 Additional headers
   4. Header Name = Authorization
   5. Value (use dynamic content) = 

@concat(
    variables('bearer_type')
    , ' '
    ,variables('bearer_token')
    ) 

• Now setup your sink

[Scott_Powell]

Unfortunately this does indeed fail at step 3. I did see an article posted here https://blog.fabric.microsoft.com/en-us/blog/data-pipelines-tutorial-ingest-files-into-a-lakehouse-f... that might show another way to do this - if so I'll update here.

Thanks!

Scott

[Scott_Powell]

p.s. and here's the section where you actually create the source connection. As you can see, until you pick a valid connection, the section where you can enter the additional header info doesn't even show.

Thx,

Scott

[ajarora]

This should work. Can you please troubleshoot if the issue is due to the specific URL you are providing, start with anonymous and any other public endpoint say https://wikipedia.org and see if that works. If you need better support, please file a support ticket for the connection creation issue, as this is a supported scenario.

[ScottPowellECU]

@ajarora Yep I'll open ticket, no worries. You absolutely can't hit the URL without the header token, and I can't enter the header token until after I can successfully connect. Kinda stuck in a loop. 

I'll update this post after working with an engineer and getting this figured out.

Thanks!

Scott

[ScottPowellECU]

p.s. yes this does work with public APIs (I used the Star Wars api to test). In our case the URL is from a vendor and it is not accessible using anonymous, you have to pass the header token.

Thanks,

Scott

[ajarora]

Ok, then that's why the connection creation is failing. The UX will attempt to establish a connection and fail if it cant (due to header missing). So ideally you need ability to "skip test connection", or add headers to Rest connection, during connection creation.

May I ask, what token needs to be sent here in the header, is it an AAD token, or some other auth means ?

[ScottPowellECU]

Yep I agree, and think the add headers to Rest connection during connection creation is the right way to go.

Re: the header itself, here's a snippet from PowerSHell that I use. The token is a specifically created large ugly string that has been created for our organization to hit the endpoint. Not Azure AD, but it is used by the api to authenticate that we're allowed to hit it.

$uri = 'https://someurl.com/api/nameofapitouse'
$headers = @{
'some_api_token' = 'HARD CODED token value given to our organization to use'
}

$DataFromAPI = Invoke-RestMethod -Uri $uri -Method Get -Headers $headers

Hope this helps!

Scott

[ajarora]

Who mints this token ? If its an AAD token, is that a user token belonging to you ? or a SPN token, belonging to an AAD enterprise App, (or MSI/UAMI flavor of identities) ?

[Scott_Powell]

Sorry you're asking questions above my knowledge level! All I can say is we reach out to this vendor and say "we need a token to hit your APIs", and then somehow we get this very long, very ugly string that when we stick in the header using the PowerShell code above it works.

The URL is hosted by the vendor, not in Azure or Google or anything as far as I know.

Hope this helps and sorry I don't know more!

Scott

[ajarora]

Good enough, thanks. Unfortunately I dont see any workaround for now in Fabric.

Please submit an idea to support new auth type in Rest connection (via auth headers ?), if there isnt one already. 

[ScottPowellECU]

Will do - thank you!

Scott