Share via

Issue with Data Flow : Data preview in Debug session for Azure SQL as source not working

Mithun.Sen 1 Reputation point
2020-12-21T11:51:54.693+00:00

I am getting below error while I am trying to validate connection for Azure SQL Database source in Data flow.

Cannot connect to SQL database: 'jdbc:sqlserver://xxx;database=xxxx', 'Managed Identity (factory name): xxx'. Please check the linked service configuration is correct, and make sure the SQL database firewall allows the integration runtime to access.

Though the ADF Integration Runtime IP's for West Europe (Azure Data Factory deployed in this Region) are added in the SQL Server IP Firewall list. Following are the Azure IPs we have white listed: "13.69.67.192/28","13.69.107.112/28","13.69.112.128/28", "40.74.24.192/26","40.74.26.0/23","40.113.176.232/29","52.236.187.112/28" .

We came to know, that we have to enable "Allow Azure services and resources to access this server" in Azure SQL Server to ensure that Data Flow is able to connect to SQL Server. (Ref - MSDN Discussion thread - https://social.msdn.microsoft.com/Forums/en-US/ba835eba-6201-435f-9647-7edad273ab3e/issue-with-data-flow-data-preview-in-debug-session-for-azure-sql-as-source-not-working?forum=AzureDataFactory)

But enabling "Allow Azure services and resources to access this server" will open another Security challenge. This option configures the firewall to allow all connections from Azure, including connections from the subscriptions of other customers (REF - https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure).

Is there is any other option apart from enabling "Allow Azure services and resources to access this server" in SQL Server, to connect SQL Server from Data Flow (ADF Pipeline -> Data Flow -> SQL Server)?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. FuchsDanielT 116 Reputation points
    2020-12-23T20:24:03.403+00:00

    Guys,

    We noticed the same message, but our workaround was first to click on TEST CONNECTION and after this, seems to work for the specific debug session.
    I said that seems to work because I noticed some data inconsistency on the records presented.

    Do you guys believe it is the same situation? Can this indicate any security issue?

    Thanks,

    Daniel

    1 person found this answer helpful.