Forum Discussion
Azure Sql Database Connector Error in Azure Data Factory and Synapse Pipelines
After changing the Lookup activity for the Built-In Pool to use a dataset connected to an Azure Synapse Analytics linked service, the errors cleared and pipelines started succeeding again.
For the primary Copy Activity against the Built-In Pool we continued using a dataset connected to an Azure SQL Database linked service and it succeeded.
Based on this, the issue seems isolated to the Azure SQL Database connector when used in a Lookup activity.
This was resolved during a screen sharing session with Microsoft support. The issue was likely due to limitations of the System Managed Identity (SMI) authentication method within Synapse Workspace. The preview option for copy and lookup activity for a dataset referencing an Azure SQL Database works with Client-Secret and User-Assigned Managed Identity (UMI) authentication options. For my company, we elected to use UMI authentication as the solution.
After changing the Lookup activity for the Built-In Pool to use a dataset connected to an Azure Synapse Analytics linked service, the errors cleared and pipelines started succeeding again.
For the primary Copy Activity against the Built-In Pool we continued using a dataset connected to an Azure SQL Database linked service and it succeeded.
Based on this, the issue seems isolated to the Azure SQL Database connector when used in a Lookup activity.
HI sphillips-analy,
I'm glad to hear you find the root causing and solve this. BTW, since your requirement is more related to azure synapse analytics, you can also post this to azure synapse forum to get further support.
Azure Synapse Analytics | Microsoft Community Hub
Regards,
Xiaoxin Sheng