Data Factory - Cannot connect to SQL Database only when triggered from Blob

Question

Also on the Microsoft Docs site here

I have a data factory pipeline that should use a Copy Data activity to insert rows from a CSV file of a blob into Azure Sql.

If I run the pipeline by clicking the "Debug" button in the designer window then it all works great. However, if I trigger the pipeline by copying the sample CSV to the blob container then I get the following error:

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: '', Database: '', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access

I have checked that the target sql server database has the option checked to "Allow Azure services and resources to access this server".

Any ideas gratefully received!

Answer 1

This error usually occurs when you are trying to access SQL data sources located in an Azure VM / VNet using Azure IR or Firewall issues

• Possible resolution/workaround:
  To be able to have a successful connection, you will need to install the Self-Hosted Integration Runtime in the Azure VM and whitelist the Machines IP address in Azure SQL VM inbound NSG rules and open Port 443 & 1433 to allow communication from Azure Data Factory to the SQL VM. Then you should be able to connect to SQL VM from Data Factory by choosing the Self-Hosted Integration Runtime in SQL Server linked service to connect.
  Here is the MS doc that explains how to create a Self-Hosted Integration Runtime: https://docs.microsoft.com/azure/data-factory/create-self-hosted-integration-runtime

Since the error message contains the string "SqlException", SQL Database the error indicates that some specific operation failed.

For more information, search by SQL error code in Database engine errors. For further help, contact Azure SQL support.

Refer: https://docs.microsoft.com/en-us/answers/questions/336067/azure-data-factory-azure-sql-connectivity-error-ch.html

Answer 2

Problem was I was missing a connection string value from the "Override template parameters" section of the release pipeline.

This meant that after deployment, the linked service did not have a connection string.

The key to Understanding this was learning of the "Switch to live mode" button from the data factory pipeline editor view:

After clicking this, I was able to browse the status of the linked service as were - rather than as per "development" mode.