TSR Session Using a External Certificate Connection

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