Create Linked services using Managed Virtual Network Azure IR to connect to Oracle on-premise database

Question

Create Linked services using Managed Virtual Network Azure IR to connect to Oracle on-premise database

John 190

Hi,

I have a requirement where I need to use mapping data flow to transform json(source is rest API) and write the data into oracle table.

I am able to connect to oracle database by using self-hosted integration runtime but not able to connect using Managed Virtual Network Azure IR.

Since I can not use self-hosted integration runtime in data flow mapping, I have no other option but to use Managed Virtual Network Azure IR.

Please let me know how to connect to oracle database using Managed Virtual Network Azure IR.

1 answer

Your answer

Answer 1

Amira Bedhiafi 31,501 Moderator

Create a Managed Virtual Network in Azure Data Factory if you haven't already. When creating an Integration Runtime, choose the 'Azure' type and enable the 'Managed Virtual Network' setting.

Verify that your on-premise network is properly configured to allow connections from Azure. This might involve setting up Network Security Group (NSG) rules or firewall rules to allow traffic from Azure's IP ranges.

Since Managed Virtual Network Azure IR is a cloud component, your on-premise Oracle database must be accessible from the cloud. This may require setting up a VPN or Azure ExpressRoute between your on-premise network and Azure.

I see you mentioned that you cannot connect to Oracle using Managed Virtual Network Azure IR directly, consider setting up a Self-hosted IR as a proxy.

Configure the Self-hosted IR within your on-premise network (where it can access your Oracle database).

Link this Self-hosted IR to your Managed Virtual Network Azure IR.

John 190 Reputation points

2024-01-01T20:32:10.88+00:00

Hi, Thanks for your help!

There is a Self-hosted IR which can access Oracle.

How do I link this Self-hosted IR to my Managed Virtual Network Azure IR?
Harishga 6,000 Reputation points Microsoft External Staff

2024-01-02T07:05:10.89+00:00

Hi John
To link a Self-hosted Integration Runtime that can access Oracle to a Managed Virtual Network Azure IR, you need to create a virtual network and a subnet, configure a virtual network gateway, enable the Managed Virtual Network feature, and modify all the integration runtime references in the linked service to the newly created Azure Integration Runtime in the managed virtual network.
Harishga 6,000 Reputation points Microsoft External Staff

2024-01-04T02:14:42.4166667+00:00

Hi John
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
John 190 Reputation points

2024-01-04T15:23:44.9366667+00:00

Hi,

In the above solution, I couldn't find any steps where it will link self hosted IR to Mvnet IR. I tried these steps but of no effect. I am still getting

Error:-
The value of the property '' is invalid: 'The remote name could not be resolved: 'xxxxxxxx.eu''.

The remote name could not be resolved: 'xxxxxxx.eu'
John 190 Reputation points

2024-01-04T15:26:06.9933333+00:00

Hi,
I am still getting error code 11402:-

The value of the property '' is invalid: 'The remote name could not be resolved: 'xxxxx.eu''.

The remote name could not be resolved: 'xxxxx.eu'
Harishga 6,000 Reputation points Microsoft External Staff

2024-01-05T03:05:40.36+00:00

Hi John
To resolve the error "The remote name could not be resolved", it means the DNS server can't find the domain name. To fix it, check the domain name, DNS settings, network connectivity, and firewall settings. If it still doesn't work, give more details to find the cause.
John 190 Reputation points

2024-01-05T09:56:03.8833333+00:00

But with same settings, self-hosted IR is able to connect but not the MVnet IR.
Harishga 6,000 Reputation points Microsoft External Staff

2024-01-05T11:30:50.5633333+00:00
Hi John
If the Self-hosted IR is able to connect to the Oracle database but the Managed Virtual Network Azure IR is not, it could be due to a few reasons:

Make sure that the Managed Virtual Network Azure IR has proper network connectivity to your on-premises network where the Oracle database is located. This may require setting up a VPN or Azure ExpressRoute between your on-premises network and Azure.

Check the DNS settings for the Managed Virtual Network Azure IR and make sure that it can resolve the domain name for the Oracle database. You can try using the IP address instead of the domain name to see if that works.

Verify that any necessary firewall rules or NSG rules are configured to allow traffic between the Managed Virtual Network Azure IR and the Oracle database.

Check the configuration of the Managed Virtual Network Azure IR and make sure that it is set up correctly to connect to the Oracle database. You can also try recreating the Managed Virtual Network Azure IR to see if that resolves the issue.

I hope this information helps you. Let me know if you have any further questions or concerns.
John 190 Reputation points

2024-01-10T23:36:28.48+00:00

Hi, In the comment below:-
"To link a Self-hosted Integration Runtime that can access Oracle to a Managed Virtual Network Azure IR, you need to create a virtual network and a subnet, configure a virtual network gateway, enable the Managed Virtual Network feature, and modify all the integration runtime references in the linked service to the newly created Azure Integration Runtime in the managed virtual network." I got to the step to configure a virtual network gateway but I do not find the option to enable the Managed Virtual Network feature. Could you please paste some screenshot that will be easier.
Harishga 6,000 Reputation points Microsoft External Staff

2024-01-11T06:59:19.19+00:00

Hi John
Enable the Managed Virtual Network feature. You can enable the Managed Virtual Network feature during the creation of the data factory or in the integration runtime. Modify all the integration runtime references in the linked service to the newly created Azure Integration Runtime in the managed virtual network.

Reference
https://learn.microsoft.com/en-us/azure/data-factory/managed-virtual-network-private-endpoint
I hope this information helps you. Let me know if you have any further questions or concerns.
John 190 Reputation points

2024-01-11T14:50:51.7833333+00:00

Hi, It seems on-pemise data access via managed Vnet IR is not possible. I tried to create managed private endpoint but oracle option is not available.
Harishga 6,000 Reputation points Microsoft External Staff

2024-01-12T03:08:24.52+00:00

Hi John
You need to create a private link service to access Oracle by a private endpoint. A private link service is a way to expose a service privately within a virtual network. By creating a private link service for Oracle, you can access it securely from your Data Factory managed virtual network using a private endpoint.
You can use an Azure Key Vault service also to save the Oracle credentials without any problems. Azure Key Vault is a secure way to store and manage secrets, such as passwords and certificates. You can store the Oracle connection string in the Azure Key Vault which you can use from your linked service if you are connecting using SID.
I hope this information helps you. Let me know if you have any further questions or concerns.
John 190 Reputation points

2024-01-14T23:04:04.3033333+00:00

Hi, Thanks for your comment! I am able to create the private link service, I want to share the private ip with admin so that they whitelist the ip. Is it the ip with Settings(NAT configuration)? under that I see a Ip with Dynamic allocation. is that correct?
Harishga 6,000 Reputation points Microsoft External Staff

2024-01-15T04:45:13.22+00:00

Hi John
Yes, you can share the private IP address assigned to the NAT configuration of your Azure resource with your admin to whitelist it. The IP address can be either dynamically or statically allocated. Dynamic allocation means that the IP address can change if the resource is stopped and started again, while static allocation means that the IP address remains the same even if the resource is stopped and started again. It's important to note that if you have multiple resources with the same NAT configuration, they will share the same private IP address.
John 190 Reputation points

2024-01-30T22:51:53.98+00:00

Hi, Now, I am getting below error:-
Any idea, what could be the problem?
Harishga 6,000 Reputation points Microsoft External Staff

2024-02-01T05:47:48.0466667+00:00

Hi John
My guess is that this could be on Oracle side and sometimes it could be intermittent/transient once due to network issues. While investigating on this error, I found below related thread from Progress Knowledgebase and seems very close to the problem you are facing and hence I recommend reviewing this thread for possible root cause and resolution for this error.

Referenced link:
https://community.progress.com/s/article/9334

I hope this information helps you. Let me know if you have any further questions or concerns.
John 190 Reputation points

2024-02-05T14:03:48.28+00:00

Hi, I checked with DBA team. It seems request is not reaching the firewall, which means it is not going out of Azure firewall.
Harishga 6,000 Reputation points Microsoft External Staff

2024-02-06T11:03:32.2266667+00:00

Hi John
As you mentioned, if the request is not reaching the firewall, it means that it is not going out of the Azure firewall. This could be a possible cause of the issue. Please ensure that the necessary ports are open on the Azure firewall to allow outbound traffic to the Oracle database.

If the issue persists, please provide more details about your setup, such as the Azure services you are using, the version of the Oracle database, and the connection string you are using to connect to the database. This will help in providing a more specific solution to your problem.

If you need any further assistance or have any additional questions or concerns, please let me know.

Share via

Create Linked services using Managed Virtual Network Azure IR to connect to Oracle on-premise database

1 answer

Your answer