REST API Authentication Issue in Microsoft Fabric Pipeline - 401 Unauthorized

[justmehere]

Issue Description:
I'm trying to integrate Acumatica ERP with Microsoft Fabric using the REST API.

1. Initial Login (Web Activity) works successfully:
URL: https://{MYBASEURL}/entity/auth/login
Method: POST
Headers:
- Accept: application/json
- Content-Type: application/json
Body:
{
"name": "[username]",
"password": "[password]"
}

The login returns successful with cookies in ResponseHeaders['Set-Cookie'].

2. Subsequent Copy Activity fails with 401 Unauthorized:
When trying to access: https://[MYBASEURL]/entity/Default/20.200.001/Customer

I've tried passing the authentication cookie from the login response:
Headers:
- Accept: application/json
- Content-Type: application/json
- Cookie: @{activity('Web_Activity_Login_Testing_1').output.ResponseHeaders['Set-Cookie']}

Error received:
"ErrorCode=RestCallFailedWithClientError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Rest call failed with client error, status code 401 Unauthorized, please check your activity settings."

Questions:
1. What is the correct way to pass authentication from the login response to subsequent API calls in Microsoft Fabric?
2. Is there a recommended approach for maintaining session/authentication when using Acumatica REST API with Microsoft Fabric pipelines?
3. Are there any specific header requirements I'm missing?

Full login response for reference is below:
{ "Response": "", "ResponseHeaders": { "Cache-Control": "private", "Set-Cookie": "ASP.NET_SessionId=XXXXX; path=/; HttpOnly; SameSite=Lax;UserBranch=1; path=/;Locale=Culture=en-US&TimeZone=GMTM0700A; expires=Thu, 31-Oct-2024 05:40:05 GMT; path=/;.ASPXAUTH=XXXXX; path=/; secure; HttpOnly; SameSite=None;ASP.NET_SessionId=XXXXX; path=/; HttpOnly; SameSite=Lax;UserBranch=1; path=/;Locale=Culture=en-US&TimeZone=GMTM0700A; expires=Thu, 31-Oct-2024 05:40:05 GMT; path=/;.ASPXAUTH=XXXXX; path=/; secure; HttpOnly; SameSite=None;requestid=XXXXX; path=/;requeststat=+st:118+sc:~/entity/auth/login+start:638656908055761270+tg:; path=/", "Date": "Mon, 28 Oct 2024 05:40:05 GMT" }, "executionDuration": 4

[FabianSchut]

Hi,

Are you able to get it working in another client like postman?

[justmehere]

Yes I am able to get it working on postman

[FabianSchut]

I think the error is in the cookie statement: @{activity('Web_Activity_Login_Testing_1').output.ResponseHeaders['Set-Cookie']}. Can you show the output of the web activity? You can blur the sensitive information.

[justmehere]

This is the output from the web activity with sensitive info blurred:
{ "Response": "", "ResponseHeaders": { "Cache-Control": "private", "Set-Cookie": "ASP.NET_SessionId=XXXXX; path=/; HttpOnly; SameSite=Lax;UserBranch=1; path=/;Locale=Culture=en-US&TimeZone=GMTM0700A; expires=Thu, 31-Oct-2024 05:40:05 GMT; path=/;.ASPXAUTH=XXXXX; path=/; secure; HttpOnly; SameSite=None;ASP.NET_SessionId=XXXXX; path=/; HttpOnly; SameSite=Lax;UserBranch=1; path=/;Locale=Culture=en-US&TimeZone=GMTM0700A; expires=Thu, 31-Oct-2024 05:40:05 GMT; path=/;.ASPXAUTH=XXXXX; path=/; secure; HttpOnly; SameSite=None;requestid=XXXXX; path=/;requeststat=+st:118+sc:~/entity/auth/login+start:638656908055761270+tg:; path=/", "Date": "Mon, 28 Oct 2024 05:40:05 GMT" }, "executionDuration": 4

[aprotteau-dac]

What were you trying to collect from Acumatica, that you couldn't just use the odata feed for?

I'm in the process of ingestion to fabric and so far I've just been using dataflow gen 2, wanted to leave off a large amount of unnecessary data from the ingestion as Acumatica is north of 4000 tables. Was just curious about the needs requirements and use case?

[FabianSchut · Accepted solution]

You are getting the cookie correct indeed. However, I can see that ADF did not support setting a cookie in a copy activity: https://learn.microsoft.com/en-us/answers/questions/1292520/how-to-add-cookie-header-in-adf-copy-act.... I think Fabric does not support it either. I think you need to set up antoher authentication at the Acumatica ERP side. I found some blogs that might help: https://help.acumatica.com/(W(5))/Help?ScreenId=ShowWiki&pageid=a8f71c44-9f5c-4af8-9d47-bc815c8a58e7
https://docs.celigo.com/hc/en-us/articles/360047399452-Set-up-an-OAuth-2-0-connection-to-Acumatica#s...

[justmehere]

Thank you so much. I opted to use the Notebook activity and wrote the python code below and I can pull the data. Thank you, this has saved me lots of time:
Attaching the python code for anyone in future:

import requests

import json

# Login to Acumatica

def login_to_acumatica():

    login_url = "https://{YOUR_BASE_URL}/entity/auth/login"

    headers = {

        "Accept": "application/json",

        "Content-Type": "application/json"

    }

    login_data = {

        "name": "XXXXX",

        "password": "XXXXX"

    }

    session = requests.Session()

    response = session.post(login_url, headers=headers, json=login_data)

    return session, response.cookies

# Get Data

def get_customer_data(session, cookies😞

    customer_url = "https://{YOUR_BASE_URL}/entity/{API_VERSION}/{}/Customer"

    headers = {

        "Accept": "application/json",

        "Content-Type": "application/json"

    }

    response = session.get(customer_url, headers=headers, cookies=cookies)

    return response.json()

# Main execution

session, cookies = login_to_acumatica()

customer_data = get_customer_data(session, cookies)

customer_data