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.
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.