loading data with ADF to azure sql db from oracle when deny public access is YES.

meroazure1244 96 Reputation points
2022-03-03T21:30:36.297+00:00

Hi,
I have azure sql db setup with deny public access and I am able to connect to the db through private endpoint and VM in the same virtual network. But I am not able to move data from oracle using azure data factory. We do have Self Hosted Integration runtime setup and working. I am able to move data when I set deny public access to NO but does not work when Set to YES.
It says following
. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,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://learn.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access).

Azure SQL Database
Azure Private Link
Azure Private Link
An Azure service that provides private connectivity from a virtual network to Azure platform as a service, customer-owned, or Microsoft partner services.
470 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,640 questions
0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,896 Reputation points MVP
    2022-03-03T22:20:19.87+00:00

    At this time Azure SQL Database needs to have Deny Public Network Access set to No and Allow All Azure Services Set to Yes for Data Flows to be able to communicate with Azure SQL Database.

    0 comments
  2. meroazure1244 96 Reputation points
    2022-03-03T22:38:49.327+00:00

    We have sensitive data that we need to load. Will that be secure if we set up Deny Public access to NO and allow azure service to YES. We want to lock down the database so that we can securely access the data.

    0 comments
  3. Alberto Morillo 32,896 Reputation points MVP
    2022-03-03T22:46:07.637+00:00

    When Deny Public access is set to "No" and Allow Azure Services is set to "Yes" then machines/Services running in Azure Environment will be able to connect. For Azure outside connections you need to specify the public IP. That is not secure. It is a current limitation with ADF in conjuntion with private endpoint.

    Let me share here with you a beautiful table describing all possible combinations between those 2 settings.

    0 comments
  4. meroazure1244 96 Reputation points
    2022-03-04T16:29:36.103+00:00

    It seems to work with 'Deny Public access to NO and Allow Azure Services to NO. I do have setup private endpoint.

    0 comments
  5. AaronHughes 391 Reputation points
    2022-03-04T16:38:51.253+00:00

    If you have sensitive data and there are restrictions on access to source and destination point then DO NOT allow all / public access

    MS have a solution for this SHIR - these are secure devices that can be stood up in your internal network to allow this access

    https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory

    the caveats to this is you need Network components too (Vnet/PrivateEndpoints)

    0 comments