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

Toad for Oracle
1

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

2

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.

3

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

4

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

5

Does Toad work if you define TNS_ADMIN here?

Windows -> My Computer -> Properties:

image
image.png1225×688 95.4 KB

6

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

1 Like
7

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

Untitled
Untitled593×678 17.3 KB

8

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

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

9

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 ?

10

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.