search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.2k stars 21.36k forks source link

Unclear of how to use TNSNAMES.ORA on the connection string #95453

Closed lBilali closed 2 years ago

lBilali commented 2 years ago

From documentation:

For advanced Oracle native connection options, you can choose to add an entry in TNSNAMES.ORA file on the Oracle server, and in Oracle linked service, choose to use Oracle Service Name connection type and configure the corresponding service name.

Since host is mandatory, what should be as host in this case? do we need to provide a port? This talks about adding the TNSNAMES.ORA on Oracle server. shouldn't this be on the machine where integrated runtime is installed?

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

CHEEKATLAPRADEEP-MSFT commented 2 years ago

@lBilali Thanks for the question! We are investigating and will update you shortly.

ShaikMaheer-MSFT commented 2 years ago

@lBilali Host name still going to be your oracle host name only. When you choose TNSNAMES.ORA file then select Oracle service name as connection type and configure corresponding service name in linked service.

lBilali commented 2 years ago

hi @ShaikMaheer-MSFT Still we are not able to use TNSNAMES.ORA

Just to explain a bit our situation: One of our providers has an Oracle Server let's call it OracleServer. On this server we don't have control since it is managed by our provider. They have given us a TNS entry which we can use.

We have a server where we have installed Oracle Client and added a TNSNAMES.ORA file with the TNS entry provided by our provider. Lets call this ClientServer Using tools like Oracle SQLDeveloper or even SSIS with Oracle Connector we can access the OracleServer from the ClientServer using TNS

Now we are trying to do the same with ADF. we have installed OnPrem integration runtime from ADF on ClientServer

We have tried to put the ClientServer as host and use the Oracle service name as connection type We would expect to work but it does not Instead we get the error below:

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

We tried OracleServer as host as well but this does not work with the error below: which makes sense since that TNS entry is not on the OracleServer but on ClientServer instead

ERROR [08001] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]TNS-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

ShaikMaheer-MSFT commented 2 years ago

@lBilali As you rightly guessed we should not use ClientServer name as host. We should consider using oracle server name as host.

Usually, tns entry may have following details in it. From that you can take respective values in to Oracle linked service in ADF image

Please Note, GitHub forum is dedicated for docs related issues. For any technical queries or clarifications, we encourage to utilize Microsoft Q & A platform. Kindly raise your query on Microsoft Q&A Platform