topic Re: Azure Sql Database Connector Error in Azure Data Factory and Synapse Pipelines in Data Warehouse

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

sphillips-analy — Tue, 05 Nov 2024 02:43:23 GMT

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

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

Anonymous — Tue, 05 Nov 2024 07:01:58 GMT

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

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

sphillips-analy — Tue, 05 Nov 2024 17:02:06 GMT

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.

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

sphillips-analy — Wed, 06 Nov 2024 01:49:27 GMT

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

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

sphillips-analy — Wed, 06 Nov 2024 02:06:50 GMT

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.

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

Anonymous — Fri, 08 Nov 2024 06:35:43 GMT

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

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

sphillips-analy — Fri, 22 Nov 2024 22:27:27 GMT

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.