How to connect synapse external table using ADF.

Question

I have Azure Synapse external tables and wanted to bring those table data into another adls account using adf.
Created Link service in ADF using authentication type as sql authentication. used admin username and password in the same. Test connection is successful

when doing copy activity using above link service encountered with below error.

Failure happened on 'Source' side. 'Type=Microsoft.Data.SqlClient.SqlException,Message=Cannot find the CREDENTIAL 'https://sapiabbid365sit001.dfs.core.windows.net/dataverse-apps-unq2dfb13cbc5b9ed11a10b000d3ab81/OptionsetMetadata/GlobalOptionsetMetadata.csv', because it does not exist or you do not have permission.,Source=Framework Microsoft SqlClient Data Provider,'

Answer 1

https://stackoverflow.com/questions/71938817/synapses-serverless-pool-read-external-tables

One needs to give the user storage blob data contributor access on the storage account and you cannot do it via SQL auth.

So please use diff auth method

Answer 2

Hello Harshraj Sawant,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you would like to know how you can connect synapse external table using ADF.

The error occurs because Synapse serverless SQL pools require a credential object in the database to authenticate with ADLS. This credential is missing or misconfigured. Also, SQL authentication does not support ADLS access; you must use AAD authentication (e.g., managed identity or AAD service principal).

The best way to resolve this is to:

A. Set Up External Table Credential in Synapse:

1. Use a Managed Identity or an AAD Service Principal for authentication.

• An example using a Managed Identity is to assign the Synapse Workspace’s Managed Identity the Storage Blob Data Contributor role on the ADLS account.

2. Create a database-scoped credential in Synapse to access ADLS:

       CREATE DATABASE SCOPED CREDENTIAL myADLSCredential
        WITH
        IDENTITY = 'Managed Identity';

3. Recreate or alter the external table to use this credential.

B. Update the ADF Linked Service:

• Modify the Synapse linked service in ADF to use Managed Identity or AAD Service Principal Authentication.
• For Managed Identity:
  • In the Synapse linked service in ADF, set the authentication type to "Managed Identity."
  • Ensure the ADF Managed Identity has sufficient permissions (e.g., Storage Blob Data Contributor) on the ADLS account.
• For Service Principal:
  • Register an application in Azure AD and provide it the Storage Blob Data Contributor role on the ADLS account.
  • Update the linked service in ADF with the service principal's client ID, client secret, and tenant ID.

C. Validate the Permissions:

• Ensure that the assigned identity (Synapse Workspace or ADF) has access to the required path in ADLS (e.g., OptionsetMetadata/GlobalOptionsetMetadata.csv). Validate this with Azure Storage Explorer or Azure Portal.

D. Test the Copy Activity in ADF:

• Reconfigure the ADF copy activity to pull data from the Synapse external table, ensuring it uses the updated linked service.

NOTE:

If the above steps are followed correctly, the external table should authenticate with ADLS using AAD authentication, resolving the error.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.