How to copy data from oracle on-prem database to Azure Synapse analytics

Paul Berrones López 0

Currently, I have configured a project in Synapse Analytics. I have the following problem: I have created a linked server that points to an Oracle database in a local environment; however, it does not allow access and returns the following error when I perform a connectivity test: "ERROR [08001] [Microsoft][ODBC Oracle Wire Protocol driver]Connection refused. Verify Host Name and Port Number. ERROR [08001] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-12203: unable to connect to destination."

It's strange because I did the same thing previously, and it worked perfectly.

Regarding this, I have a question: is it possible to know from which IP the connection request is made in Azure Synapse? I ask this because the people who manage the database I want to access have a firewall, and they create rules in it to allow connectivity.

What other options do I have to carry out this exercise of connecting the Oracle database with Azure Synapse Analytics?

Any help is welcome.

Bhargava-MSFT 31,031 Reputation points Microsoft Employee

2024-08-07T19:32:23.99+00:00

Hello Paul Berrones López,

Can you whitelist the SHIR Machine IP in the synapse end? Please reach out to the team managing the Synapse environment and request them to add the SHIR machine IP to the whitelist.
Paul Berrones López 0 Reputation points

2024-08-07T19:44:02.3433333+00:00

Hello @Bhargava-MSFT

I have already added the public IP of the SHIR to the whitelist of the Synapse workspace
Bhargava-MSFT 31,031 Reputation points Microsoft Employee

2024-08-07T22:41:41.54+00:00
Can you run network monitoring tools like Netmon to verify that the correct IP addresses are whitelisted in the firewall settings?

also please check the below

Double-check that the host name and port number for the Oracle database are correct. The error message suggests that there may be an issue with the host name or port number.

Ensure that the Oracle database is running and accepting connections. Try connecting to the database from a different machine to see if you can establish a connection.

Verify that the ODBC driver for Oracle is installed and configured correctly on the machine running the SHIR.

Confirm that the credentials used to connect to the Oracle database are correct and have the necessary permissions to access the database.

Check that there are no network issues preventing the SHIR machine from reaching the Oracle database.

Check the Oracle database logs for any errors or connection attempts. This can provide more insight into why the connection is being refused.

Test the connection from another machine outside of Synapse but on the same network as the SHIR to determine if the issue is with Synapse or the network configuration.
Paul Berrones López 0 Reputation points

2024-08-08T14:22:47.21+00:00

It seems there has been a misunderstanding. I am not currently using an SHIR configuration. The Synapse environment I am working on only has a built-in Integration Runtime defined. The connection I made to another Oracle database worked without any issues, and setting up SHIR was not necessary. However, now that I need to make another connection to a different Oracle database, the connection is not working.

This is the connection that works fine

This is the connection with problems
Paul Berrones López 0 Reputation points

2024-08-09T21:42:35.88+00:00

Both are on-premises instances.

Thank you for the article, I'm gonna try it

1 answer

Bhargava-MSFT 31,031 Reputation points Microsoft Employee

2024-08-09T21:04:39.32+00:00

Can you confirm if both of the oracle is cloud instances?

Please follow this below KB article and see if it helps

https://community.progress.com/s/article/2968
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to copy data from oracle on-prem database to Azure Synapse analytics

1 answer

Your answer