Azure Sql Database Connector Error in Azure Data Factory and Synapse Pipelines

[sphillips-analy]

Started receiving this error when connecting to an Azure SQL Database in both Synapse Pipelines and Azure Data Factory.

I can provide more info about the environment and configuration if asked for.

What are some possible causes and solutions?

Thanks

[sphillips-analy · Accepted 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.

[sphillips-analy · Accepted 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.

[sphillips-analy · Accepted 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.

[Anonymous]

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

[sphillips-analy]

Just discovered this also impacts doing lookups against our Built-In Pool as well.

[sphillips-analy]

This is a Synapse pipeline with 1 activity to lookup values from a configuration table located on an Azure SQL Datbase. Let me know if there's more specifics you'd like to know about.

[Anonymous]

HI @sphillips-analy,

What type of pipeline are you worked? The Azure synapse pipeline or fabric data pipeline? Can you please share some more detail about these?

Regards,

Xiaoxin Sheng