Forum Discussion

oscar_lee's avatar
oscar_lee
Occasional Reader
Mar 11, 2025

Azure ADF ServiceNow connector can't retrieve table columns but same login can do in REST API

I have tried to create a pipeline using copy activity to extract data from a table in our ServiceNow dev platform. I have first used the latest version of the ServiceNow connector. However, it didn't work. When I tried to import schema, it shows below error message:

Failed to load. The API request to ServiceNow failed. Request Url: https://airtrunkautemp.service-now.com/api/now/table/sys_dictionary?sysparm_query=name%3dfacilities_request^ORname%3dsm_order^ORname%3dtask, Status Code: Forbidden, Error message: {"error":{"message":"Insufficient rights to query records","detail":"Field(s) present in the query do not have permission to be read"},"status":"failure"} Activity ID: 5a99e871-893d-4426-809e-0b22654248f8

Then I tried to use the legacy version of the ServiceNow connector, extract full table data using query. After I executed the pipeline, only 1 column sys_id is returned.

I have contacted the ServiceNow support for the issue, they checked and got back me the access login has no issue. Then I wrote python to use REST API to retrieve data from the same table, it works, I could extract all table columns without the insufficient rights issue.

Does anyone have this experience before? How did you solve it?

azure
Data + Storage
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Mar 12, 2025

    Consider this before contact Microsoft support:

     

    1. Verify Permissions and Access Control Lists (ACLs)

    • Even though ServiceNow support confirmed that your login has no issues, the ADF connector might be encountering stricter ACLs for certain fields or tables.
    • Check the ACLs for the sys_dictionary table and ensure that the user account has explicit read permissions for all fields being queried.

    2. Use a Custom Query
    If the latest ServiceNow connector fails, try using a custom query in the legacy connector:

    sysparm_query=name=facilities_request^ORname=sm_order^ORname=task

    Ensure that the query only includes fields you have permission to access.

    3. Enable Debugging and Logs

    • Enable detailed logging in ADF to capture the exact API request and response. This can help identify whether the issue is with the connector or the ServiceNow instance.

    4. Use REST API as a Workaround

    • Since the REST API works with your Python script, you can use ADF's HTTP connector to call the REST API directly.
    • Configure the HTTP connector with the ServiceNow API endpoint and include authentication headers.
    • Parse the JSON response using ADF's mapping data flow or custom scripts.

    5. Check Connector Version and Updates

    • Ensure that you are using the latest version of the ServiceNow connector in ADF. Microsoft frequently updates connectors to address compatibility issues.

Resources