Microsoft Fabric Data Factory, Oauth2 and Xero

[JohnLMcGuire]

Signed up for a trial of Fabric and trying to ingest data using OAuth2 REST API from Xero. There was a Xero connector in Azure Data Factory but this was not carried over into Fabric so it seems you use the Web activity. 

I have a Postman collection so can connect to Xero and get data using Postman. I have a seperate pipeline which copies data from another anonymous REST API and copies to a Lakehouse.

I'm struggling to make the leap into getting a pipeline working in Fabric, with an Oauth2 REST API like Xero, especially the authentication part and getting tokens. I can't seem to find any good documentation/learning videos describing how you set this up. Can anyone point me in the direction of any content that could show me how to do this ? 

[v-gchenna-msft]

Hi @JohnLMcGuire ,

Thanks for using Fabric Community,

As per my understanding you want to create Rest API Pipeline in Fabric Data Factory.

I assume you might have 2 APIs, API_1 will get a bearer token and other API_2 will get result(final data) on passing bearer token along with header.

Lets assume API_1 requires client_id and client_secret to get bearer token:



Step 1: Connection - Add your base URL here.
Step 2: Relative URL - Add your relative URL here.
Step 3: Body (Main point) - Can add your client_id and client_secret here in json format

{

    "grant_type":"client_credentials",

    "client_id":"abcdefg",

    "client_secret":"123ghy456"

}

Step 4: Header: Add Content-Type : application/json

Lets assume API_2 requires bearer token to get result (final data):

Step 5: Create a New Copy Activity.
Step 6:  Link a Copy Activity to this Web Activity.
Step 7: Creation a Rest API connection with the base URL.
Step 8: Configure connection type and base url (API_2)

Step 9: !Important Configure your header with Authorization: Bearer <token>



Step 10: !Important (inorder to pass token dynamic way, not manually)
Configure value with add dynamic content  Bearer @{activity('User_Login_Token').output.data.Token}

Note: .data.Token is as per my json output, you can write your expression based on your json output from API_1.

Hope this is helpful. Please let us know incase of any queries.

[JohnLMcGuire]

Thanks for the help. It's appreciated.

Unfortunately this didn't work. I get a 400 Bad Request error.

I can get a token in Postman using a collection supplied by Xero. I am failing at the first step with Fabric which is to get the access token as far as I can tell. 

I set a connection as https://login.xero.com. 

These are the settings in Postman which does get an access token. Below that I have shown what I have in Fabric Web Activity. 

Postman settings which work above.

Settings in Fabric Web Activity which do not work. 

I'm npot sure how I set up the web activity to match what I can achieve in Postman when accessing a REST Oauth2 API

[v-gchenna-msft]

Hi @JohnLMcGuire ,

Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

[JohnLMcGuire]

Hi @v-gchenna-msft 

An update on this which may make thing simpler. There are various options for flows for Xero. I had been using Code Flow (best for web server apps which can store a client secret securely). I have now pivoted to use a Custom Connection (best for back end machine to machine integrations) - Custom Connections — Xero Developer.

This does simply things but I am still having an issue. However this is closer to your original response. 

Xero states there should be a request sent like this from their documentation.

I have set up Postman and can get a token. The request sent from Postman (which works) has the following details with some key redacted for security in this post.

"Request Headers": {
"authorization": "Basic NzlGMz****************************************************S05WZlZWZ0dv",
"user-agent": "PostmanRuntime/7.37.3",
"accept": "*/*",
"postman-token": "01d653e3-4ef3-4bb0-81b6-4a17a0826adf",
"host": "identity.xero.com",
"accept-encoding": "gzip, deflate, br",
"connection": "keep-alive",
"content-type": "application/x-www-form-urlencoded",
"content-length": "91",
"cookie": "_abck=AF0E************************FMS9R2bye5qr638Id5Q4TNh/MYozMjPXKy~-1~-1~-1; Device=20ccc0a79b494c1e8c748fe285446a60"
},
"Request Body": {
"grant_type": "client_credentials",
"scope": "accounting.transactions.read accounting.contacts.read"
},

I have a web activity in Fabric and have copied over each of the header values (Content-Type and Authorization) directly from the values shown above. This fails with an 'Invalid Client' which seems to imply that Xero has a problem with the client_id /client_secret.  The Input log of the Fabric Activity shows this which looks to have identical values to Postman but it works on Postman but does not on Fabric.

{
"method": "POST",
"headers": {
"Content-Type": "application/x-www-form-urlencoded",
"Authorization": "Basic NzlGMz*************************************************S05WZlZWZ0dv"
},
"relativeUrl": "/connect/token",
"body": {
"grant_type": "client_credentials",
"scope": "accounting.transactions.read"
}
}

Comparing this Fabric generated Input value to Postman I cannot see why Fabric doesn't connect. The Authorization string has been copied verbatim from Postman so should be identical (note I have redacted the real value with '*' chars) in Postman and Fabric. 

Could Fabric be corrupting the Authorization value ? I'm copying and pasting to get the value in there for experimenting but could it be picking up hidden characters (although the Fabric input Authorization string looks to fine).  

Sorry for the long reply but hope this might help with triage plus I think Xero is a very popular accounting system so I'm sure once this work then this post might be useful to others. 

[v-gchenna-msft]

Hi @JohnLMcGuire 

Apologize for the issue you are facing. The best course of action is to open a support ticket and have our support team take a closer look at it.

Please reach out to our support team so they can do a more thorough investigation on why this it is happening: Link 

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Hope this helps. Please let us know if you have any other queries.

[v-gchenna-msft]

Hello @JohnLMcGuire ,

We haven’t heard from you on the last response and was just checking back to see if got a chance to create a support ticket.
After creating a Support ticket please provide the ticket number as it would help us to track for more information.

[JohnLMcGuire]

@v-gchenna-msft 

We have discussed with a Microsoft Partner Manager and they are putting us in contact with someone who can help. When I have an update I will share the ticket number or the resolution in this thread. 

[v-gchenna-msft]

Hi @JohnLMcGuire ,

Thanks for the response. Please let me know incase if you have further queries.
Also please do share that resolution with the community as it can be helpful to others .