Azure Data Factory Oracle TNS Connection

Question

Hello,

I'm trying to set up an Azure Data Factory connection to an on-premise Oracle database. I have already installed the Data Factory self-hosted integration runtime on my data transfer server, and I confirmed that the Oracle database is accessible from the data transfer server using the following TNS connection string called via 'sqlplus user@mydb'.

After reading the documentation, I still cannot figure out what the right combination of settings is to set up the Oracle linked service.

I also read this answer and tried the alternateservers option mentioned, but it still doesn't work. Can anyone help me figure out what the right way to enter the settings is?

mydb =
  (DESCRIPTION =
    (SOURCE_ROUTE = yes)
    (ADDRESS_LIST =
      (FAILOVER = on)
      (LOAD_BALANCE = on)
      (ADDRESS = (PROTOCOL = tcp)(HOST = pool1.domain.com)(PORT = 30421))
      (ADDRESS = (PROTOCOL = tcp)(HOST = pool2.domain.com)(PORT = 30421))
    )
    (ADDRESS = (PROTOCOL = tcp)(HOST = primary.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = service_name)
    )
  )

Answer 1

Just had another call with the Azure support team and we finally figured out a solution. Here are the steps:

1. Create a new linked service
2. Select the runtime you'll be using to connect to the DB server
3. Scroll to the bottom where it says "Advanced" and expand that option
4. Click the checkbox that says "Specify dynamic contents in JSON format"
5. In the JSON section use the following format:

{
    "properties": {
        "type": "Oracle",
        "typeProperties": {
            "connectionString": "ServerName=<The server name configured in the TNSNAMES.ORA>;TNSNamesFile=D:\\TNSNAMES.ORA;User Id=<username>;Password=<password>"
        }
    }

ServerName should be whatever name you use to refer to your database in your tnsnames file.

Also note that all of the backslashes in your TNSNamesFile path should be escaped with an extra slash (\).

Once you have your JSON complete, click "Test Connection".

If someone at Microsoft can please add this example to the official documentation for Azure Data Factory connections to Oracle that would be awesome. This is not at all clear (at least to me) in that documentation.

https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle?tabs=data-factory

Answer 2

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