Toad World® Forums

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

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)
)

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

Try this:

  1. Start Toad, close the login window if it appears automatically
  2. Go to Main Menu -> Database -> Spool SQL -> Spool to Screen
  3. try to make your connection.
  4. 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.

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

Copyright (c) 1982, 2017, Oracle. All rights reserved.

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

Copyright (c) 1982, 2017, Oracle. All rights reserved.

Copyright (c) 1982, 2017, Oracle. All rights reserved.

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

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

Does Toad work if you define TNS_ADMIN here?

Windows -> My Computer -> Properties:

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

1 Like