In our project we have a Oracle linked service created using connection type as SID. We have a requirement to connect to a Oracle database using Service name only. We want single linked service that can access Oracle database using SID or Service name. This is possible by using Azure Key valut but the option given by my manager is to use tnsnames.ora file and connect to oracle database by using existing SID linked service to an Oracle database by Service name. How can we use single linked service?

In our project we have a Oracle linked service created using connection type as SID. We have a requirement to connect to a Oracle database using Service name only. We want single linked service that can access Oracle database using SID or Service name. This is possible by using Azure Key valut but the option given by my manager is to use tnsnames.ora file and connect to oracle database by using existing SID linked service to an Oracle database by Service name. How can we use single linked service?

• azure
• azure-data-factory
• azure-synapse
• tnsnames
• linked-service

• can you try In the "Connection" tab, select "Use connection string" and click on "Edit". Replace the SID value in the connection string with the name you gave to the new entry in the tnsnames.ora file? – Dileep Raj Narayan Thumula Commented Feb 11 at 2:55

1 Answer 1

Below are the steps where you can create a single linked service able to access an Oracle database using either SID or Service Name

The tnsnames.ora file contains network service names mapped to connect descriptors & make sure that file is correctly configured with both the SID and Service Name entries.

configuration for tnsnames.ora:

ORCL_SID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_host)(PORT = 1521))
    (CONNECT_DATA =
      (SID = your_sid)
    )
  )
ORCL_SERVICE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = your_service_name)
    )
  )

Next, in Azure Synapse, create an Oracle linked service and specify the connection type as Service Name. In the Service name provide the value as tnsnames.ora file.

The connection string in the Linked service should be like below:

{
  "type": "Oracle",
  "typeProperties": {
    "connectionString": "Host=your_host;Port=1521;ServiceName=your_service_name;User Id=your_username;Password=your_password;"
  }
}

Reference: Troubleshooting-Oracle-linked-service-with-tnsnames.ora-configuration-file Connecting to Oracle: A Guide to Using Service Names Over SIDs