Unable to connect to On-Premise Oracle Database. It was working since last week.

Question

Dear Sir/Madam

Till yesterday I was able to connect my On-Promise Oracle Database using Dynamic Linked Service. My Oracle Database credentials are stored in Key Vault.

Suddenly I started getting below error which we have not made any change either in Azure or Oracle.

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
Activity ID: 48832a60-4788-491b-b469-8367bf7b1ec5.

Answer 1

Hello @Singh, Akhilesh K ,

Thanks for the question and using MS Q&A platform.

My understanding is that you ADF On prem Oracle linked service connection was running fine and all of sudden it started throwing the error saying connection refused and you confirm that you haven't made any changes to your ADF settings or on the Oracle side. And also, you are using AKV to get the connection details. Please correct if I misunderstood your problem.

If you haven't done any changes to ADF linked service configuration or Oracle side, then there should be some changes happened from Azure ADF product side or Oracle side. But so far, we haven't received similar issue related to ADF oracle connection failed with above errors, which makes me think that the issue could be on Oracle end.

Before you start investigating from Oracle side, I would recommend hardcoding the linked service credentials and other configurations and try to see if your connection is successful or not. If it is not, then it confirms that there might be something on the Oracle side and not on Azure side. In case if your test connection is successful by hardcoding the values then it means that the issue is related to dynamic configuration (parameterization or AKV configuration). Then I suggest retrying with a different secret and reconfiguring the linked service with relevant info and see if that helps.

Another thing I could think of from ADF standpoint is that you might be using SHIR for connecting to your On Prem Oracle server. I see that there is a new version of SHIR available in the download center. In case if your SHIR is auto upgraded to the latest version and if you started noticing this issue after the upgrade then that could be another possible reason behind this issue but not 100% sure. I recommend validating it first and if you could confirm that the issue started only after the SHIR upgrade, then that might give us hint on where to troubleshoot. But please do validate and let us know your findings so that we escalate and involve relevant product owners to further review this.

My guess is that this could be on Oracle side and sometimes it could be intermittent/transient once due to network issues. While investigating on this error, I found below related thread from Progress Knowledgebase and seems very close to the problem you are facing and hence I recommend reviewing this thread for possible root cause and resolution for this error.

Referenced link: "Connection refused. Verify Host Name and Port Number." error

Here are possible error messages and their root cause and resolutions:

Error Messages:

[DataDirect][ODBC DB2 Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
[DataDirect][ODBC DB2 Wire Protocol driver]Unexpected Network Error. ErrNum=10038

or

[DataDirect][ODBC DB2 Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
[DataDirect][ODBC DB2 Wire Protocol driver]Unexpected Network Error. ErrNum = 10053

or

[DataDirect][ODBC Oracle Wire Protocol driver]Connection refused. Verify Host Name and Port Number. (0)
[DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-12203: unable to connect to destination (-1)

or

[DataDirect][ODBC Progress Wire Protocol driver]Connection refused. Verify Host Name and Port Number.

Possible Root Cause:

• This error has many causes
• 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

Possible Resolution:

• Check for ODBC DSN corruption

Create a new ODBC DSN with the minimum connection options required to connect to the database and test connect using the sample applications like ODBC Test, example, etc.

• Verify with the database administrator

If the specified Host Name / IP address and port number are correct.
If the user credentials are valid and/or correct.
If the database service is still listening on the specified port, including secondary login brokers that may not have started.
If there are errors in the database logs (e.g. OpenEdge databasename.lg file) when attempting to connect.
What the database version is that you connect to and verify if this version is supported by the driver.
If the database is stressed which could result in refused connection requests. The connection option Connection Retry Count may need to be set to a higher value than the default 0 when the cause is a stressed database.

• Specifically for SQL Server, check the below database information to determine if additional connection options need to be set.

1) Is the connection attempt being made to a regular SQL Server instance or a named instance.
2) Is TCP/IP enabled?
Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <SQL Server instance> and ensure TCP/IP is enabled.
3) Is Encryption enabled?
Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <SQL Server instance> and see if Force Encryption value is set to Yes.
If so, what protocols are allowed by the OS?
See link https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/dn786418(v=ws.11)?redirectedfrom=MSDN

• Verify with the network administrator

If the specified IP-address / Host name can be reached from the client machine where the driver is installed and that the hostname is pointing to the correct machine.
If there are client / server / network firewalls, packetshapers,.. which might block communication.
If the client and server machines are on different subnets and a router blocks communication.

• Verify with the system administrator

If there are firewall / virus scanners active on the client or server side which block communication. Firewalls may come with the system (e.g. windows 2003, 2008,..) or may be included as part of anti-virus software, VPN software.

• Clustered environments

Verify that all nodes or server hosts that the connection may be rerouted to are reachable by the client machine.

Hope this will info help. In case if the issue still persists consistently, I recommend filing a support ticket for deeper investigation. Do let us know if you have further query.

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Sometimes the connectionstring are bit wonky

try the following:

This was giving error in my PROD, but not in UAT or QA:

host=oracledb.europe.myserver.com:1648;serviceName=mydb;user id=myuser;Password=mypassword

and i resolved with below

host=oracledb.europe.myserver.com;port=1648;serviceName=mydb;user id=myuser;Password=mypassword

Also you do need SHIR.