Connection issue with Oracle Autonomous Cloud (ADW) and Azure Data Factory

Keshaw Bhardwaj 0

Hi,

I am trying to connect Oracle Autonomous Database (ADW) with Azure Data Factory using self hosted integration runtime. After setting up Oracle DataDirect wire protocol 8.0 driver still i am unable to connect. i am getting this error below

ERROR [08001] [Microsoft][ODBC Oracle Wire Protocol driver]ORA-12203: unable to connect to destination

If you have any solution regarding this, please provide

Thank You

Harishga 5,990 Reputation points Microsoft Vendor

2024-05-10T13:18:42.0266667+00:00

Hi @Keshaw Bhardwaj

Thank you for reaching out. It seems that you are facing an issue with connecting Oracle Autonomous Database (ADW) with Azure Data Factory using self-hosted integration runtime.

Firstly, check if the Oracle server is accessible from the machine where the self-hosted integration runtime is installed. You can do this by pinging the Oracle server’s IP address or hostname. If it's not accessible, we need to check the network connectivity and firewall settings to ensure that the firewall on the Oracle server allows connections on the port that the Oracle service is using.

Next, check the connection settings in the Oracle DataDirect wire protocol driver, such as the service name, username, and password. Ensure they match the Oracle server’s configuration. If you’re using dynamic configuration or Azure Key Vault for storing credentials, try hardcoding the connection details temporarily to rule out any issues with dynamic configuration.

Confirm that the ODBC driver is correctly installed and configured on the machine where the self-hosted integration runtime is installed. Also, check if the Oracle server is up and running without any issues.

If the integration runtime was recently updated, consider reverting to a previous version that was known to work, as some users have reported issues after updating.

Lastly, review the logs for the self-hosted integration runtime for any additional error messages or warnings that could provide more insight into the problem.

Reference
https://community.progress.com/s/article/000057351
https://learn.microsoft.com/en-us/answers/questions/1460952/azure-data-factory-error-connecting-to-oracle-adw

I hope these steps help you resolve the issue. If you need further assistance, please let me know.
Harishga 5,990 Reputation points Microsoft Vendor

2024-05-13T04:44:19.4866667+00:00

@Keshaw Bhardwaj
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.
Keshaw Bhardwaj 0 Reputation points

2024-05-13T04:50:52.49+00:00

I tried the solution you suggested but still it is not working and i am getting the same error.
Harishga 5,990 Reputation points Microsoft Vendor

2024-05-13T05:37:11.91+00:00
Hi @Keshaw Bhardwaj
Here are the steps you can follow to resolve the ORA-12203 error when connecting Oracle Autonomous Database with Azure Data Factory using self-hosted integration runtime:

Verify that the Oracle ADW is accessible over the network from the machine hosting the self-hosted integration runtime using tools like tnsping.

Confirm that the TNS Listener on the Oracle server is running and properly configured to accept connections.

Examine the tnsnames.ora and listener.ora files for any incorrect entries or syntax errors.

Ensure that the self-hosted integration runtime is up-to-date and try rolling back to a previous version that worked if it was recently updated.

Check if the Oracle Client and the DataDirect wire protocol driver are correctly installed and configured on the machine with the self-hosted integration runtime.

Verify whether you are using the correct Service Name or SID in your connection string.

Review the firewall settings on both the Oracle server and the machine with the self-hosted integration runtime to ensure the required ports are open.

Reference:
https://learn.microsoft.com/en-us/answers/questions/710762/unable-to-connect-to-on-premise-oracle-database-it
I hope this helps you resolve the issue. If you have any further questions or concerns, please let me know.
Keshaw Bhardwaj 0 Reputation points

2024-05-15T09:18:48.27+00:00

ERROR [28000] [Microsoft][ODBC Oracle Wire Protocol driver]Cannot load trust store. SSL Error Queue:

ERROR [08001] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-12203: unable to connect to destination

I am getting this error now @Harishga , can you please provide any resolution.

1 answer

Harishga 5,990 Reputation points Microsoft Vendor

2024-05-15T10:22:51.6066667+00:00

@Keshaw Bhardwaj
The errors you are facing are related to ODBC connections to an Oracle database and involve SSL and connectivity issues.

The first error, "ERROR [28000]," states a problem with the SSL configuration, specifically with the trust store file that contains the trusted SSL certificates. To resolve this issue, you should ensure that the trust store file is present and correctly specified in the connection settings, verify that the trust store file has the correct permissions and is accessible by the ODBC driver, and check if the trust store file is in the correct format and contains the appropriate certificates.

The second error, "ERROR [08001] ORA-12203," signifies that the client could not connect to the Oracle database. Possible solutions include confirming that the network connection to the Oracle server is active and stable, verifying that the hostname and port number in the connection string are correct, ensuring that the Oracle listener is running and properly configured, and checking the TNSNAMES.ORA file for correct configuration details.

It is important to make sure that the ODBC drivers are compatible with the Oracle version you are using and that all connection details are accurate. If the problem persists, you need to consult with a network administrator.

I hope this helps you resolve the issue. If you have any further questions or concerns, please let me know.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

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

Share via

Connection issue with Oracle Autonomous Cloud (ADW) and Azure Data Factory

1 answer

Your answer