Existing Dataset in ADF pipeline not working for Data Flow

Question

I have a sql server db on a vm that I remote into from the Azure portal using bastion. The DB has private endpoints and so forth as part of it's security arrangements.

I have set up a linked service in the ADF that allows me to connect pipelines with copy activities to read and write data to the tables in the DB. The permissions in the linked service are below:

The data sets used in the pipelines leverage this linked service and all works great.

I have created a data flow to create some more complex joins between tables in the SQL DB. When I create a new dataset or use an existing dataset using the above linked service - all datasets and the linked service work for the pipeline - but in the dataflow I get the connection error when I test connection.

If I open this dataset from this screen and preview data it works fine. The error message I am getting seems to indicate the linked service connection doesn't work as it needs a private endpoint, but i see the private endpoint name in linked service screenshot above.

Cannot connect to SQL database: 'jdbc:sqlserver://server.database.windows.net;database={sqldb-database-prd}', 'Managed Identity (factory name): DF-datafactory'.
[SQL Exception]Error Code:47073, Error Message: Reason: An instance-specific error occurred while establishing a connection to SQL Server.
Connection was denied since Deny Public Network Access is set to Yes (https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access).
To connect to this server, use the Private Endpoint from inside your virtual network (https://docs.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database).

Answer 1

Hi @Binway ,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this!

Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue:

Created linked service of a Database which has private endpoints in the ADF that allows to connect pipelines with copy activities to read and write data to the tables in the DB. When creating a new dataset or use an existing dataset using the above linked service - all datasets and the linked service work for the pipeline - but in the dataflow getting the connection error like below when testing source or sink connection.

Cannot connect to SQL database: 'jdbc:sqlserver://server.database.windows.net;database={sqldb-database-prd}', 'Managed Identity (factory name): DF-datafactory'. [SQL Exception]Error Code:47073, Error Message: Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://docs.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database).

Solution :

Checked the settings at the data flow level and changed to the Private endpoint IR.

The integration runtime used by your data flow should have the necessary network access to the SQL Server. It should be set to the runtime for the Vnet.

Please click Accept Answer and kindly upvote it so that other people who faces similar issue may get benefitted from it.

Answer 2

@Binway

It looks like you're encountering a connectivity issue with your Azure Data Factory (ADF) data flow when trying to connect to your SQL Server database.

Please Check the below steps and confirm us

• Ensure that the private endpoint is correctly configured and associated with your SQL Server database.
• Check that the private endpoint is in the same virtual network as your ADF managed virtual network.
• Verify that the NSG rules allow traffic between the ADF managed virtual network and the SQL Server private endpoint.
• Since the error message mentions that "Deny Public Network Access" is set to Yes, ensure that your ADF is configured to use the private endpoint for connectivity. You can find more details on this setting here.
• Ensure that the managed identity used by ADF has the necessary permissions to access the SQL Server database.

Could you please provide the detailed error logs if possible.

Hope this helps. Do let us know if you any further queries.

Answer 3

Checked the settings at the data flow level and changed to the Private endpoint IR.