SQLPLUS not getting credentials from ewallet.p12 file

Question

Followed https://docs.oracle.com/cd/E78494_01/aip/pdf/1411/html/ig/aip-ig-apx_wallet.htm guide and created ewallet.p12 (also generates cwallet.sso), sqlnet.ora, and tnsnames.ora:

PS C:\Users\myname\Oracle> echo $env:ORACLE_HOME
C:\app\oracle\product\19.0.0\client_1
PS C:\Users\myname\Oracle> echo $env:TNS_ADMIN
C:\Users\myname\Oracle\my_admin

Then tested my credentials, and tnsnames.ora successfully:

sqlplus user/password@tns_alias

However, removing user and password from above (expecting to get them from ewallet.p12 file) errors out:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul xxxxxxxxx
Version 19.3.0.0.0

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

ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:

and then entering username and password when prompted, gives me tns adapter error:

Enter user-name: user
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Following is sqlnet.ora content:

SSL_CLIENT_AUTHENTICATION=FALSE
SSL_SERVER_DN_MATCH=TRUE

WALLET_LOCATION = 
    (SOURCE = 
    (METHOD=FILE) (METHOD_DATA = (DIRECTORY = C:\Users\myname\Oracle\my_wallet)))

TRACE_LEVEL_CLIENT=16
TRACE_FILE_CLIENT = client.trc
TRACE_DIRECTORY_CLIENT=%USERPROFILE%\ORACLE

and the content of wallet folder:

PS P:\> ls C:\Users\myname\Oracle\my_wallet\


    Directory: C:\Users\myname\Oracle\my_wallet


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       30/07/2020   7:59 PM           2509 cwallet.sso
-a----       30/07/2020   7:39 PM              0 cwallet.sso.lck
-a----       30/07/2020   7:59 PM           2464 ewallet.p12
-a----       30/07/2020   7:39 PM              0 ewallet.p12.lck

This seems to be very simple but not sure what I've missed?

Answer 1

When you use a WALLET for authentication using a password store it is a good advice to include two additional files ( sqlnet.ora and tnsnames.ora ) different to the ones you use normally for connections with passwords.

Copy the tnsnames.ora and sqlnet.ora to the same wallet directory and follow this steps:

3.Edit the sqlnet.ora to look like this

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY =  C:\Users\myname\Oracle\my_wallet )
     )
    )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

3.Your new tnsnames.ora must contain the entries for the wallet ( either service_name or sid )

MY_EXAMPLE=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = server  )(PORT = port ))(CONNECT_DATA =(SERVER = DEDICATED)(SID = databasesid)))

4.SET the TNS_ADMIN to the location of the new sqlnet and tnsnames files

SET TNS_ADMIN=C:\Users\myname\Oracle\my_wallet

5.The directory with all files should look like ( In my case I use Linux )

-rw-------. 1 orafil finance    0 Feb  6 16:30 ewallet.p12.lck
-rw-------. 1 orafil finance    0 Feb  6 16:30 cwallet.sso.lck
-rw-rw-r--. 1 orafil finance  235 Feb 18 10:26 sqlnet.ora
-rw-rw-r--. 1 orafil finance  454 May 14 11:35 tnsnames.ora
-rw-------. 1 orafil finance 2872 May 14 14:26 ewallet.p12
-rw-------. 1 orafil finance 2917 May 14 14:26 cwallet.sso

Then connect

sqlplus user/@MY_EXAMPLE