Unable to connect ADF to Oracle 19c DB

Venkata Ravikumar Chebrolu 5

Hi ,

We are trying to create oracle 19c Database connection as a source in Azure Data Factory and getting below popup error message.

ERROR [08S01] [Microsoft][ODBC Oracle Wire Protocol driver]Socket closed.

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

Activity ID: 3cefa339-9457-48e2-89db-fac0eb3fd8fa.

Note: We were able to connect and work Oracle 12c db. But issue with 19c db only.

Thank You,

Venkat

AnnuKumari-MSFT 32,906 Reputation points Microsoft Employee

2023-09-01T15:34:04.3066667+00:00
Hi Venkata Ravikumar Chebrolu ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As I understand your query you are facing an issue while trying to create an Oracle 19c database connection as a source in Azure Data Factory.

Socket closed error is usually observed in the Open Database Connectivity (ODBC) driver because the underlying Transmission Control Protocol (TCP) connection has been terminated by either party (client or server).

There can be a number of reasons for that, for example:

network failure

firewall timeout

the database unexpectedly terminated the connection

account permissions

Check to see if database is SSL enabled or not, if database is SSL enabled make sure required SSL connection options are set.

Check the ODBC Trace and see where the issue lies.

Also, since you mentioned, connection got successfully established with ODBC 12c using ADF but not with ODBC 19c database, it means Oracle connector in ADF is not having issue, but the issue is with the source . Kindly check with the source team and look for guidance. Thankyou
Martijn 0 Reputation points

2023-10-23T15:11:21.62+00:00

I have the same issue and was wondering if you found a solution to this problem?

2 answers

Amira Bedhiafi 24,531 Reputation points

2023-09-01T11:02:28.5566667+00:00

When attempting to connect Azure Data Factory (ADF) to Oracle 19c and encountering an ORA-12203 error, you should check several key areas:

Start by confirming the compatibility of the ADF Oracle connector with Oracle 19c. Ensure Oracle 19c is network-accessible from ADF, which might require IP whitelisting in Oracle's firewall.

Then examine the Oracle listener configuration and the TNSNAMES.ORA file for accuracy. Verify that you're using an ODBC driver compatible with Oracle 19c and review the connection string details.

Finally, inspect configurations in both Oracle 19c (especially if there are deprecated features from 12c) and ADF's linked service, and consider consulting Azure Support if the issue persists.
Please sign in to rate this answer.
Venkata Ravikumar Chebrolu 5 Reputation points

2023-09-13T10:56:16.35+00:00

Thank you for your reply!!

I have whitelisted Oracle server IP in IR network and upgraded IR to latest version. But still facing the same issue.

Thank You,

Venkat

Amira Bedhiafi 24,531 Reputation points

2023-09-13T11:00:17.4733333+00:00

Can you provide more details ?

Venkata Ravikumar Chebrolu 5 Reputation points

2023-09-13T11:24:55.25+00:00

Hi Amira,

We are trying to create a linked service using oracle connector.

When we created a connection with Oracle 12c Database, we were able to connect and now using that.

But while trying to create a new connection with Oracle connector for 19c Database, we were not able to create and it's giving following error.

Error code9603

DetailsERROR [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 Activity ID: 6f4d8e1c-5880-40a0-b168-914f417e99c5.

We have taken below actions but still no use.

White listed Oracle DB server IP at network level.

Updated IR servers to latest versions.

Please advice.

Thank You,

Venkat

Thank You,

Venkat

Amira Bedhiafi 24,531 Reputation points

2023-12-02T14:51:22.57+00:00

https://community.progress.com/s/article/9334
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Venkata Ravikumar Chebrolu 5 Reputation points

2023-09-12T14:15:07.6933333+00:00

Thank you for your reply!!

I have whitelisted Oracle server IP in IR network and upgraded IR to latest version. But still facing the same issue.

Thank You,

Venkat
Please sign in to rate this answer.
Livia Regina Bijelli Ferreira 0 Reputation points Microsoft Employee

2023-12-02T01:17:01.1966667+00:00

Hi!

Try to enable EncryptionMethod=1 using a connection string in JSON Advanced configuration:

{ "properties": { "type": "Oracle", "typeProperties": { "connectionString": "Host=<host>;Port=<port_number>;ServiceName=<service_name>;EncryptionMethod=1;User Id=<user>;Password=<password>;" } } }

https://learn.microsoft.com/en-us/azure/data-factory/data-movement-security-considerations

Note

To enable encryption in transit while moving data from Oracle follow one of the below options:

In Oracle server, go to Oracle Advanced Security (OAS) and configure the encryption settings, which supports Triple-DES Encryption (3DES) and Advanced Encryption Standard (AES), refer here for details. ADF automatically negotiates the encryption method to use the one you configure in OAS when establishing connection to Oracle.

In ADF, you can add EncryptionMethod=1 in the connection string (in the Linked Service). This will use SSL/TLS as the encryption method. To use this, you need to disable non-SSL encryption settings in OAS on the Oracle server side to avoid encryption conflict.
Sign in to comment

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

Share via

Unable to connect ADF to Oracle 19c DB

2 answers

Your answer