Share via

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,700 questions
0 comments

7 answers

Sort by: Newest
Most helpful Newest Oldest
  1. meroazure1244 96 Reputation points
    2022-03-08T20:27:32.993+00:00

    This is for ADF. I have Link Service for SQL DB and Oracle DB. Linked Service for Azure SQL DB uses Integration runtime with Managed Virtual Network whereas Linked Service for Oracle On Prem used SHIR. With this setting
    will I be able to move data from oracle to azure when Deny Public Access is set to YES.
    Do I need to whitelist IP address of SHIR in order for this to work?

    0 comments
  2. meroazure1244 96 Reputation points
    2022-03-04T21:50:51.44+00:00

    I am using Self Hosted Integration Runtime to pull data from oracle on prem to azure sql db. It does not work if I set Deny Public Access to YES. I am using Private endpoint on azure sql db to connect to azure sql database. Am I missing some steps here? It only works when I setup Deny Public Access to NO. There is Vnet and Private end point setup. For oracle connection, I am using Self Hosted IR and for Azure SQL DB connection, I am using Azure IR. Does it need to be SHIR for both connections?

  3. 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
  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. Alberto Morillo 32,981 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