Skip to Main Content
  • Questions
  • MY_WALLET_DIRECTORY in TNSNAMES not working as expected

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Narendra.

Asked: June 11, 2021 - 2:22 pm UTC

Last updated: September 26, 2022 - 10:26 am UTC

Version: 19.3

Viewed 1000+ times

You Asked

Hello Chris/Connor,

I am trying to use the option MY_WALLET_DIRECTORY in TNSNAMES file in order to avoid having to specify WALLET_LOCATION in SQLNET.ORA file. However, it fails with ORA-01017: invalid username/password; logon denied error. Tracing the SQLNET (from client side) reveals that it never attempts to access wallet when specified as MY_WALLET_DIRECTORY but works fine when used conventionally (as WALLET_LOCATION in SQLNET.ORA file).
Can you please help me figure out how to get this working?

In MOS note The Impact of the Sqlnet Settings on Database Security (sqlnet.ora Security Parameters and Wallet Location) (Doc ID 1240824.1), it appears to mention that using MY_WALLET_DIRECTORY is supported (excerpt below)

#8. Client settings.

OCI Clients are using the same sqlnet.ora and wallet settings as the database server.
However, a very good hint to use, when multiple wallets should be used by the same client and using multiple TNS_ADMIN environment variables can become a real pain (one example would be when using ODP.NET applications requiring multiple SSL certificates to logon to multiple services, especially on application servers), then this could be easily solved using a tnsnames.ora syntax similar to the one below:

<tns_alias> = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = <machine_name>)(PORT = <port_number>)) (CONNECT_DATA = (SERVICE_NAME = <service_name>) ) (SECURITY = (MY_WALLET_DIRECTORY = <some_wallet_directory>)))"


Below is my attempt
First, to prove that the wallet is set up correctly and WALLET_LOCATION mentioned in SQLNET.ORA.

C:\Users\JoeBlog\TNSHome\ScottWallet>copy ..\sqlnet.ora .
        1 file(s) copied.

C:\Users\JoeBlog\TNSHome\ScottWallet>set TNS_ADMIN=C:\Users\JoeBlog\TNSHome\ScottWallet

C:\Users\JoeBlog\TNSHome\ScottWallet>tnsping DB_SCOTT

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 11-JUN-2021 15:02:41

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

Used parameter files:
C:\Users\JoeBlog\TNSHome\ScottWallet\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbscan.oracle.world)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbserv.oracle.world.com)) (SECURITY = (MY_WALLET_DIRECTORY = C:\Users\JoeBlog\TNSHome\ScottWallet)))
OK (70 msec)

C:\Users\JoeBlog\TNSHome\ScottWallet>sqlplus /@DB_SCOTT

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 11 15:02:51 2021
Version 19.3.0.0.0

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

Last Successful login time: Fri Jun 11 2021 10:03:06 +01:00

Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> show user
USER is "SCOTT"
SQL> exit
Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


Now, when I try to use only MY_WALLET_DIRECTORY in TNSNAMES (but nothing in SQLNET.ORA)

C:\Users\JoeBlog\TNSHome\ScottWallet>cd ..

C:\Users\JoeBlog\TNSHome>set TNS_ADMIN=C:\Users\JoeBlog\TNSHome

C:\Users\JoeBlog\TNSHome>tnsping DB_SCOTT

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 11-JUN-2021 15:03:39

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

Used parameter files:
C:\Users\JoeBlog\TNSHome\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbscan.oracle.world)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbserv.oracle.world.com)) (SECURITY = (MY_WALLET_DIRECTORY = C:\Users\JoeBlog\TNSHome\ScottWallet)))
OK (90 msec)

C:\Users\JoeBlog\TNSHome>sqlplus /@DB_SCOTT

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 11 15:04:12 2021
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: ^C


and Connor said...

I replicated your findings on various versions and platforms - all took no notice of the MY_WALLET_DIRECTORY

I've reached out to the security team to see if they have any insight, but as it stands, I suspect that this is functionality that was never implemented.

I'll update this answer if I get any more information from the security team.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Strange

Narendra, July 01, 2021 - 8:29 am UTC

Hello Connor,

Thank you for looking into this.
It is really strange that such a feature has been documented and blogged about but doesn't work.
I first came across it in https://blogs.oracle.com/dev2dev/whats-new-in-193-and-183-jdbc-and-ucp and got excited as we do have a use case for it.
I am unable to post any comments on that blog post too....if the feature does not work then I guess at least official blogs and documentation should be corrected accordingly.

Thanks
Connor McDonald
July 06, 2021 - 1:46 am UTC

I'll come back to this post if I get any updates from the security team

A reader, July 14, 2021 - 5:19 pm UTC

@narendra, was anything else in the original SQLNET.ORA? On windows, mind you, I successfully use MY_WALLET_DIRECTORY, the difference being that I also have SSL_VERSION and SSL_CLIENT_AUTHENTICATION parameters in my TNSNAMES as well, which I also migrated out of SQLNET.ORA into my TNSNAMES.ORA. (Also, my connection is with a database user account, not authenticating via Kerberos, AD, etc., that could be a difference too.)

I don't see documentation specifying exactly what parameters are required with respect to wallets, but it's a possible explanation that something else from the original SQLNET.ORA file (if indeed something else was there) is still required. Or that somehow, the assumed default value if omitted is a mismatch.

Other tests to eliminate the differences between my environment and yours: Do you maybe have database user account to test with? And/or a Windows machine?

Good luck!
Connor McDonald
July 15, 2021 - 3:38 am UTC

If you could post a sample, I am sure that would help others (and me!)

Re: MY_WALLET_DIRECTORY in TNSNAMES

Narendra, August 05, 2021 - 7:10 am UTC

Hello Connor,

Did you manage to hear anything on this one? (I know....you said you would respond if you hear and I am not being impatient...just asking in case you know anything more).
Would be a really useful feature if it works as advertised.
Connor McDonald
August 06, 2021 - 1:46 am UTC

Sorry - I should have updated the thread.

The current understanding is that the MY_WALLET_DIRECTORY *does* work for only related to SQLNet encryption activities, not for the storing of credentials.

(Investigation is ongoing but that has been our observations so far)

DBA

Mark Carton, February 17, 2022 - 1:48 pm UTC

I hit this issue too and found this thread.

I found a "fix" for the issue by creating a credential wallet in the $TNS_ADMIN directory.
mkstore -wrl /home/oracle/wallet -create
mkstore -wrl /home/oracle/wallet -createCredential conn1 user1 pass1
mkstore -wrl /home/oracle/wallet -createCredential conn2 user2 pass2

So in TNS_ADMIN I have sqlnet.ora with
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/oracle/wallet")))
then in tnsnames.ora on each connection string have
(MY_WALLET_DIRECTORY=/home/oracle/wallet/'<per connection>'/)

from this we can then use
sqlplus /@conn1 - works
sqlplus /@conn2 - works

Hope this helps.
Connor McDonald
February 24, 2022 - 7:17 am UTC

Nice detective work! Thanks for passing this on.

Re: Mark Carton

Narendra, February 25, 2022 - 4:52 pm UTC

Hello Mark/Connor,

Can you please elaborate how this solves my original question? My original question was about having the ability to point to different wallets or different directories containing (different) wallets, by using MY_WALLET_DIRECTORY in TNSNAMES.ORA but without having to rely on SQLNET.ORA for wallet location.

Your example appears to create single wallet and a SQLNET.ORA which points to the directory containing that single wallet.

What am I missing?

Jack, April 19, 2022 - 7:04 am UTC

Hello Narendra

were you able to find solution for same? I am facing exact same issue though few blogs suggest MY_WALLET_DIRECTORY should work, for me, I tried all possible configurations none seems to be working.

A reader, September 21, 2022 - 1:56 pm UTC

I was able to get it working. My situation is a bit different cause I'm making an app in C#, but I think the example will help others. The below settings will allow 2 different users to connect to the DB determined by the connection string.

<configSections>
  <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.122.21.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
 </configSections>
 <connectionStrings>
  <add name="connStr1" connectionString="Data Source=uSmFomName1;User ID=/;" providerName="System.Data.OracleClient" />
  <add name="connStr2" connectionString="Data Source=uSmFomName2;User ID=/;" providerName="System.Data.OracleClient" />
 </connectionStrings>
 <oracle.manageddataaccess.client>
  <version number="*">
   <dataSources>
    <dataSource alias="uSmFomName1" descriptor="
       (DESCRIPTION=
        (ADDRESS=
         (PROTOCOL=TCPS)
         (HOST=hostX)
         (PORT=2484)
        )(CONNECT_DATA=
         (SERVICE_NAME=sidX)
        )(SECURITY=
         (MY_WALLET_DIRECTORY=C:\temp\OracleWallet_1)
        )
       )" />
    <dataSource alias="uSmFomName2" descriptor="
       (DESCRIPTION=
        (ADDRESS=
         (PROTOCOL=TCPS)
         (HOST=hostX)
         (PORT=2484)
        )(CONNECT_DATA=
         (SERVICE_NAME=sidX)
        )(SECURITY=
         (MY_WALLET_DIRECTORY=C:\temp\OracleWallet_2)
        )        
       )" />
   </dataSources>
   <settings>
    <setting name="SQLNET.AUTHENTICATION_SERVICES" value="(TCPS)" />
   </settings>
  </version>
 </oracle.manageddataaccess.client>

Connor McDonald
September 26, 2022 - 10:26 am UTC

Thanks for passing this along

To: a reader

Narendra, September 26, 2022 - 10:32 am UTC

Thank you for this.
I have to say I honestly believe there is something fundamentally wrong with the implementation of MY_WALLET_DIRECTORY.
How else does one explain different behaviour under different circumstances?
It is a shame that Oracle appears to have (again) released something as "cool new feature" that does not work as advertised.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database