Azure Data Factory Oracle connection through self Hostes IR using TNSNAMES

Question

Azure Data Factory Oracle connection through self Hostes IR using TNSNAMES

Bell, Bo 1

Hi,

We are trying to connect an Asure Data factory pipeline to an On-prem Oracle database server. The server is "hidden" behind network gateways, and need a specific connection string setup to work.

To do this we install the Oracle client driver and set up a TNSNAMES.ORA file with the correct connection string. This works fine locally on the SHIR machine ( the Oracle tnsping are able to connect ) but failes when we try to make the connection from Data Factory.

msg:
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

The Tnsnames ora file looks like :

We have tried several configuration of the connection , but it seems like its always the Microsoft driver responding; is there any way to force the SHIR to use the Oracle native driver (which is used by tnsping) ? or any other suggestion to make this work ?

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-11-03T12:18:23.927+00:00

Hi @Bell, Bo ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
Zakaria Muhammad 161 Reputation points

2022-08-26T05:14:07.253+00:00

I have similar issue earlier where I have to revert the auto updated Integration runtime to older version to make it work. now when i try to upgrade the IR it failed again.

I am going back to older version which is working and will search for resolution. if anyone resolve the issue please share the solution.

IR working : 5.12.7984.1
Not working anything after version 5.17.8154.2 and above.

TIA

4 answers

Your answer

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-11-03T12:18:23.927+00:00

Hi @Bell, Bo ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
Zakaria Muhammad 161 Reputation points

2022-08-26T05:14:07.253+00:00

I have similar issue earlier where I have to revert the auto updated Integration runtime to older version to make it work. now when i try to upgrade the IR it failed again.

I am going back to older version which is working and will search for resolution. if anyone resolve the issue please share the solution.

IR working : 5.12.7984.1
Not working anything after version 5.17.8154.2 and above.

TIA

Answer 1

Hi @Bell, Bo , @Raine Holm

i have had the same issue and was able now to solve it with adding an additional connection property "alternateservers" Here you can provide multiple additional servers for you connection.

Property name: AlternateServers
Property value: (HostName=<secondary host>:PortNumber=<secondary port>:ServiceName=<secondary service name>)

Or if you use a SID connection type
Property value: (HostName=<secondary host>:PortNumber=<secondary port>:SID=<secondary SID name>)

You can even add mutliple additional servers only by concatenating the entries with addtional ":"

I found that this solution is already described in the documentation in the section "Linked service properties" :
https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle?tabs=data-factory

Here you can find a screenshot, how to add that additional parameter:

pgb 15 Reputation points

2023-08-11T13:49:36.42+00:00

@Cornelius Munz Can you post an example of what your tnsnames.ora (with anything sensitive changed) file looks like? I still can't get mine to work even with the alternateservers option in place. Would love to see what your tnsnames looks like so I can match up where to put stuff in the ADF configuration dialog.

Answer 2

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi @Bell, Bo ,

Thank you for posting query in Microsoft Q&A Platform.

Above error may occur because of any of following reasons.

Specified Host Name is incorrect or resolves to the wrong machine
Specified IP-address is unreachable
Specified port number is incorrect
Database service is not up and running
Firewall / Virus scanner on the client / server / network is blocking communication
Client and Server machine are on different subnets and a router blocks communication
Unsupported database version.
Wrong user name and/or password
Corrupted ODBC Data Source Name (DSN) entry

Could you please cross check your case with above possibility of error causes?

Please Note, Oracle connector in Azure data factory supports below oracle database versions. Click here to know more about Oracle connector in Azure data factory.

Oracle 19c R1 (19.1) and higher
Oracle 18c R1 (18.1) and higher
Oracle 12c R1 (12.1) and higher
Oracle 11g R1 (11.1) and higher
Oracle 10g R1 (10.1) and higher
Oracle 9i R2 (9.2) and higher
Oracle 8i R3 (8.1.7) and higher
Oracle Database Cloud Exadata Service

Bell, Bo 1 Reputation point

2021-11-05T07:26:08.073+00:00
Due to the fact that the tnsping works ok and that we are able to logon and run queries from the machine where SHIR are installe, we have cluded that its not an Orcle og network problem.

Howveer, it seems that when selecting an Oracle connector in data factory, its is not possible to specify a complex connection string as listed above. We have tried several wayes arounbd the problem :

enter the connection string in the json code

using a tns name as connection string

...

What evcer we do, we get an error message from the Microsoft driver, which is what we believe is the problem - the Microsoft ODBC driver does not support a tns connection string. We are using the oracle native ODBC driver on the machine, and it seems like we are unable to trigger the usage of this driver.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-11-09T11:09:10.123+00:00

Hi @Bell, Bo ,

I would encourage you to have support ticket for this for deeper investigation. Below is the link which explains about creating support ticket. Thank you.
https://learn.microsoft.com/en-us/azure/azure-portal/supportability/how-to-create-azure-support-request
Raine Holm 1 Reputation point

2021-12-22T12:27:09.7+00:00

Did you find solution for this issue? I am having same kind of setup and getting the same error from ADF when connecting to Oracle source.

Answer 3

Daniel Guardieiro 0

Hello,

I'm preparing a new connection from ADF to a oracle 19c database via Integration runtime.

Before I start, I would like to know if is mandatory have a oracle client in the Server I have the integration runtime running.

Thank you in advance.

Have a nice day

Answer 4

pgb 15

Please see updated process for connecting via TNSNames.ora file here: https://learn.microsoft.com/en-us/answers/questions/1344398/azure-data-factory-oracle-tns-connection?page=1&orderby=helpful

Share via

Azure Data Factory Oracle connection through self Hostes IR using TNSNAMES

4 answers

Your answer