Using Toad for Oracle Xpert (64-bit) with DB Admin Module version 16.2.98.1741 (recently upgraded from 13.3).
I have two direct connections. One is using a digital certificate, the other using a password. Using Toad I spooled the SQL to the screen during both connections. The redacted connection strings are provided below.
External Certificate Connection -
Connect: [my_schema_name]@(DESCRIPTION=(ADDRESS=(PORT=2484)(HOST=url)(PROTOCOL=TCPS))(CONNECT_DATA=(SID=my_sid)))
Password Connection -
Connect: my_schema_name@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
No issues so far, and I can successfully execute queries in the main Editor windows for both connections. When using the certification connection and I attempt to create a Toad Script Runner session (Editor -> Execute SQL via TSR), then I get the following error for the attempted TSR session: “ORA-12547: TNS:lost contact”. The connection remains viable in the Editor windows in the main the application. I can successfully establish a TSR session using the password connection. It is just the certificate connection TSR session that fails to connect.
I tried a variation of the certificate connection to use the SERVICE_NAME instead of the SID, but got the same results.
Connect: [my_schema_name]@(DESCRIPTION=(ADDRESS=(PORT=2484)(HOST=url))(PROTOCOL=TCPS))(CONNECT_DATA=(SERVICE_NAME=my_service)))
All connections are using Connect using Oracle Client which is pointing to a local 19.3.0.0 Oracle InstantClient64. Same issue occurs when using a 12.1.0.2 (64-bit) and a 19.14.0.0 (64-bit).
The issue seems to only occur when using a Direct connection. I do not have the issue when using a TNS connection.
Using SQL*Plus directly and avoiding Toad for Oracle, I can successfully connect using the certificate connection.
Your assistance with establishing a TSR session using a certificate connection would be much appreciated.
TSR has its own setting for "connect using an Oracle client". It doesn't necessarily follow what Toad is doing. I wonder if it's unchecked in TSR. No-client mode doesn't support TCPS so that could be it.
Look in TSR -> Edit -> TSR Options -> Oracle -> Use Oracle Client. If you need to change it - disconnect in TSR. Then change the option and close TSR. After that, you can re-launch it from Toad.
It would be nice if the no-client option could support TCPS. In our government operation here, we are required by policy to use TCPS, and it would be nice if we could do so without an Oracle Client.
Cheers,
Russ
Hi Russ,
The ability to connect w/o a client is a feature of our 3rd party connectivity components. I can't really speak to why TCPS is not supported or if/when they will support it, but it does support Oracle Encryption and checksum, so it is possible to use connect w/o the client in a secure manner. Just not TCPS. Details in this post.
-John
LOLs. Pointing me to my own earlier thread. Nostalgia.
Unfortunately, we have to deal with the DOD STIG which requires certificate-based database connections. Encryption used to be enough but not anymore.
Cheers,
Russ
1 Like
That's funny, I didn't even remember that was you on the first post - I just have a shortcut for it.
I can ask our 3rd party vendor about TCPS, but I'm guessing they would have added it if they could.
Maybe it is in their future plans, but I suspect it is not easily doable.
Cheers,
Russ
Thank you for the tips. Sorry for the delayed reply.
The TSR Option "Use Oracle Client" was and still is checked.
Given Toad.exe's proxy session is connected via an external certificate connection with TCPS (using a Direct connection - not using a TNS connection), when a QSR.exe session is launched from that Toad session's editor window (Editor -> Execute SQL via TSR), then it appears QSR (TSR) is trying to use TCP (instead of the TCPS) and is throwing the "ORA-12547: TNS:lost contact".
This is new behavior with 16.2 that was not present with 13.3.
If I manually edit the TSR Oracle Logon "Database" field, I can successfully establish a proxy session using the certificate connection over TCPS.
If one uses a TNS connection that uses TCPS when establishing the connection in Toad.exe, then the QSR session launched from Toad does work. Note: an LDAP connection was not tested.
Therefore, it appears to be something in the hand-off from Toad.exe to QSR.exe where the TCPS protocol is no longer being honored by QSR, but only when using a Direct connection (not a TNS connection).
Thank you in advance for your continued assistance in resolving this on-going issue.
Hi Space Cord,
Are you able to test this in beta?
It's ok if not. I can dig deeper after the break, and I'm logging it as a bug for now, so I don't forget about it. But it's possible this has been fixed already in beta.
Also, I suspect that if you put the connection info into tnsnames.ora and use that instead of a direct connect, then there will be no problem.
Therefore, it appears to be something in the hand-off from Toad.exe to QSR.exe
Sounds like you hit the nail on the head here. Should be an easy fix once I see what's going on.
-John
Ok, I see what's happening here.
When you connect via direct mode, Toad connects behind the scenes with full TNS syntax, like this:
user/pass@(DESCRIPTION=(ADDRESS=(PORT=...)(HOST=...)(PROTOCOL=TCPS))(CONNECT_DATA=(SERVICE_NAME=...)))
But we send the EZConnect (like below) when connecting to TSR.
user/pass@host:port/sid
This syntax does not support protocol (except in 19c clients and newer).
I'll make a change to next beta so that we'll send the full TNS syntax to TSR. That will resolve this.
-John
Are you able to test this in beta?
Unfortunately, I am not able to test in Beta. Sorry.
Also, I suspect that if you put the connection info into tnsnames.ora and use that instead of a direct connect, then there will be no problem.
That's correct, there are no problems using TNS connection. From earlier in the thread:
The issue seems to only occur when using a Direct connection. I do not have the issue when using a TNS connection.
To be clear, the issue does occur with an Oracle 19c client. I understand that the logic currently uses EZConnect syntax regardless of Oracle client and your fix avoids the different Oracle client versions by instead using the full TNS syntax regardless of Oracle client.
Thanks for identifying and correcting.
1 Like