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

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.