Connecting with TCPS - ora-28759: failure to open file

erickg · October 22, 2019, 6:40pm

Trying to connect to a Oracle database listener that has TCPS enabled for network encryption. On the Toad client (v12.10) when making a new connection, I'm using Direct option, when pressing more button there is 'TCPS' option in protocol drop-down. I'm get following error.

ORA-28759: failure to open file

in the sqlnet.ora of the Oracle client home TNS_ADMIN directory, it has appropriate wallet directory and DB certificate loaded.

sqlnet.ora
...
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA256,SSL_RSA_WITH_AES_256_GCM_SHA384)
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = E:\app\oracle12\product\12.1.0\client_1\network\wallet)
)

JohnDorlon · October 22, 2019, 7:26pm

I think it's a config problem, but not sure what the solution is.

Try this:

Start Toad, close the login window if it appears automatically
Go to Main Menu -> Database -> Spool SQL -> Spool to Screen
try to make your connection.
the spool SQL output will then show the connect string that Toad builds out of the parameters in the "direct" tab. For example:
Connect: APP_SCHEMA@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=HOST02)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=SH1)))

You can then take that string and try it connecting with SQL*Plus, taking Toad out of the equation. If you discover that Toad is building the string incorrectly, let me know and we'll get it fixed.

erickg · October 22, 2019, 8:55pm

Tried connection using command line using TCPS connection:

PS C:> sqlplus SYSTEM/xxxxx@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=WDC1ORADBEBSD01)(PORT=2516))(CONNECT_DATA=(SERVICE_NAME=EBSDBA)))"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 22 13:27:36 2019

ERROR:
ORA-28759: failure to open file

Got the same error
So i set TNS_ADMIN environment variable

$ENV:TNS_ADMIN="E:\app\oracle12\product\12.1.0\client_1\network\admin"

TNS_ADMIN has sqlnet.ora which has WALLET_LOCATION (contains DB certificates)

NOW able to connect
PS C:> sqlplus SYSTEM/xxxxx@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=WDC1ORADBEBSD01)(PORT=2516))(CONNECT_DATA=(SERVICE_NAME=EBSDBA)))"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 22 13:27:36 2019

Last Successful login time: Sun Oct 20 2019 11:44:39 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit

erickg · October 22, 2019, 8:59pm

It seems in TOAD when using direct option with TCPS, the sqlnet.ora file in TNS_ADMIN is not being picked up. For TCPS connections, TNS_ADMIN env is needed to locate sqlnet.ora so it can find WALLET files, otherwise will get error below.

ERROR:
ORA-28759: failure to open file

JohnDorlon · October 22, 2019, 9:00pm

Does Toad work if you define TNS_ADMIN here?

Windows -> My Computer -> Properties:

erickg · October 22, 2019, 9:15pm

Yes after setting system wide ENV variable for TNS_ADMIN to correct location, the TCPS connection in Toad is now working.

Nabilah · November 4, 2021, 10:44am

i have same error and did configure the setup as per suggested above but still having same error

JohnDorlon · November 4, 2021, 11:34am

@Nabilah does the problem also happen with SQL*Plus?

If so, you may have missed something when setting up your Oracle client.

Nabilah · November 4, 2021, 1:32pm

yes seems i cant open sqlplus as well.

is it correct the sqlplus should located at C:\app\oracle\product\19.0.0\client_1\bin ?

JohnDorlon · November 4, 2021, 1:41pm

It's hard to tell. It looks like you have at least 3 Oracle clients installed.

Do you have a coworker who can connect to the database? If so, talk to them and set things up the way they did.