question

DavidvanderVelden-7155 avatar image
1 Vote"
DavidvanderVelden-7155 asked DavidvanderVelden-7155 edited

Copy-Activity using TLS Certificate+ OAUTH Token

For a customer, I need to get data from a Web-API using Data Factory.
The cusromers partner has shared:

1) Certificate for mutual TLS
2) Client-ID and Client-Secret to retrieve OAUTH-token

I want to use the 'Copy-Activity' to get the data, but I have not been able to get this to work!
To prove I can do it with Azure Data Factory, I have implemented both:

  • Success: Using a Web-Activity to retieve Token and Web-Activity to retieve api-data**

  • Failed: Using a Web-Activity to retrieve Token and Copy-Activity to retieve api-data**

First I will show how I set up the succesfull pipeline (WHICH I DO NOT WANT TO USE!).

As shown here: both activities work correctly

129875-image.png

The first activity gets the Bearer Token.
The second activity uses the Bearer Token
The second activity also uses the Certificate supplied by the Customers' Partner:

![129809-image.png][3]

Please notice that both the Token and Certificate are filled in.

Finally, I get the Json data from the API.

129868-image.png

Great! Only, I do not want to use this, because I want to directly load into SQL Server and not pre-stage in a storage account or something.

Next I will show method 2, which I cant get to work!!

As shown, the first activity gets the Token, but the second activity (Copy) fails:

129856-image.png

To enter the certificate, I need to use a HTTP Linked Service. So I set that up:

129810-image.png

Notice that the test states: 'Connection Successful'
Next I set up the Dataset:

129921-image.png

And finally the Copy-Activity:

129883-image.png

And when I run the pipeline, the response is a HTTP 403.

Please note that I have an other customer, which does not use a certificate, where this pipeline does work!
This leads me to believe that this combination: 1) HTTP Linked Service 2) Certificate 3) Additional Authorization Header does not work.
It looks to me, that in the background, the request does not use the additional header somehow.
But,... I can not prove this. Even went so far to try and capture/decipher the packages using WireShark, but that out side of my skill-set.
My theory is, that the header is being omitted somehow, due to the option 'certificate' in the HTTP Linked Service.

I've been struggling with this for a week now. Think I tried everything within my reach.
Is it possible for a MS Support engineer to reach out to me and show me what I am doing wrong? Or confirm that Azure Data Factory does not support this?

Thank you.

BR,
David




[3]: /answers/storage/attachments/129825-image.png

azure-data-factory
image.png (5.1 KiB)
image.png (61.1 KiB)
image.png (16.5 KiB)
image.png (5.8 KiB)
image.png (60.5 KiB)
image.png (7.8 KiB)
image.png (30.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @DavidvanderVelden-7155 and welcome to Microsoft Q&A. Thank you for your detailed research and process.

Your theory of the Bearer header somehow not getting passed correctly for that specific combination sounds very plausible to me. I cannot yet say for certain whether that is the case. I am not part of the ADF development team.

I plan to test your theory by using an Azure Function app. The app will require a certification, like yours does. The app will read all headers and echo them in the response body. This way I can see what is being sent. Function App isn't my area of expertise, and I have never used a cert with it before, so this might take a day or two. (Assuming it can use certs, need to check.)

Once I have proof, I can involve the development team.

I seem to vaugely recall a similar situation, long ago, where the combination of multiple methods didn't work. That might have been using a different dataset.

0 Votes 0 ·

@DavidvanderVelden-7155 , just to clarify,

You are using HTTP dataset for source, and SQL on sink. The SQL has no issues, correct? The 403 is coming from the HTTP source side, right?

0 Votes 0 ·

1 Answer

DavidvanderVelden-7155 avatar image
0 Votes"
DavidvanderVelden-7155 answered DavidvanderVelden-7155 edited

Hi Martin,

Thanks for your reply.
Last week I have submitted a support ticket for this.

Just came off call with member of product team.
I have shared my screen and showed the case in detail.
His first conclusion was that there was something not working correctly in the HTTP-Linked Service and how it uses the certificate.

He will investigate further and will come back to me.

I will post conclusion here when I have it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.