ODAC 12c Release 4 ODP.NET managed driver with Oracle Wallet gotcha
We’re pleased that Oracle fixed the issue with the ODP.NET managed driver in the newly released ODAC 12c Release 4, that prevented us to use Oracle Wallet as a password store in the past. We have several .NET applications that’re read-only, intended to be used by multiply end users. Users don’t need personal database accounts. Instead, we want our application to read app-user credentials from Oracle Wallet.
Until ODAC 12c Release 4 we were unable to use ODP.NET managed driver with Oracle Wallet file method. The issue that we had was limited to the managed driver, unmanaged driver has no problem reading Oracle Wallet password store.
After we saw new feature announcement on page 3: Secure External Password Store SEPS….”ODP.NET, Managed Driver supports storing password credentials using SEPS.”, we decided to test this.
We created and configured Oracle Wallet:
cmd> mkstore -wrl D:\OW\ACME -create cmd> mkstore -wrl D:\OW\ACME -createCredential apptns appuser apppassword Then we configured sqlnet.ora: SQLNET.AUTHENTICATION_SERVICES = (NONE) NAMES.DIRECTORY_PATH= (TNSNAMES) WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\OW\ACME))) SQLNET.WALLET_OVERRIDE = TRUE and tnsnames.ora: APPTNS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = acmedb.host.com) (PORT = 1521) ) ) (CONNECT_DATA = (SID = ACME) ) ) Finally we tested the connection with: sql> connect /@apptns Connected.
So far, so good. Oracle Wallet is prepared to be used by .NET application with managed driver 12c Release 4.
We saved sqlnet.ora and tnsnames.ora in the same directory with Oracle Wallet files. Relevant part of the application config looked like this:
<oracle.manageddataaccess.client> <version number="*"> <settings> <setting name="TNS_ADMIN" value="D:\OW\ACME"/> <setting name="TraceLevel" value="7" /> <setting name="TraceOption" value="1"/> <setting name="TraceFileLocation" value="D:\TRACE" /> </settings> </version> </oracle.manageddataaccess.client>
Just for the record, we could set everything (sqlnet.ora parameters + tns alias) in app.config as well:
<oracle.manageddataaccess.client> <version number="*"> <settings> <setting name="TraceLevel" value="7" /> <setting name="TraceOption" value="1"/> <setting name="TraceFileLocation" value="D:\TRACE" /> <setting name="SQLNET.AUTHENTICATION_SERVICES" value="null" /> <setting name="WALLET_LOCATION" value="(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\OW\ACME)))" /> <setting name="SQLNET.WALLET_OVERRIDE" value="TRUE" /> <setting name="NAMES.DIRECTORY_PATH" value="(TNSNAMES)" /> </settings> <dataSources> <dataSource alias="APPTNS" descriptor="APPTNS = (DESCRIPTION =(ADDRESS_LIST=(ADDRESS =(PROTOCOL = TCP)(HOST = acmedb.host.com)(PORT = 1521)))(CONNECT_DATA=(SID=ACME)))"/> </dataSources> </version> </oracle.manageddataaccess.client>
For the purpose of testing managed driver with Oracle Wallet, we wrote simple applet that takes Username, Password and TNS alias from the end user:
displays connection string:
and displays a message “Connection Opened!” if login was successful. But instead, we’re surprised by ORA-01017 error:
I will spare you with all the dirty details of troubleshooting that we went through, finally we decided to turn on sqlnet trace by adding three parameters to the app.config, namely; TraceLevel, TraceOption and TraceFileLocation (note that Oracle documentation at the time of this writing has a typo: TraceFileLocations. Note the “s”, this is wrong!).
And found this:
2015-11-25 13:08:47.270153 TID:3 (PRI) (CP) OracleConnectionImpl.Connect() OracleConnectionImpl.Connect => Querying Data Source:APPTNS 2015-11-25 13:08:47.291153 TID:3 (NET) (SQLNET) SEPS userid is null. 2015-11-25 13:08:47.291153 TID:3 (NET) (SQLNET) SEPS password is null.
What the hack? We have APPTNS in our Oracle Wallet. Or do we?
cmd> mkstore -wrl D:\OW\ACME -listCredential Oracle Secret Store Tool : Version 220.127.116.11 Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 1: apptns appuser
Is it possible that managed driver is handling tns alias in case sensitive manner? Bingo.
Even thought it doesn’t matter how you connect with the sqlplus, like:
sqlplus /@APPTNS sqlplus /@apptns sqlplus /@Apptns ...
in all three cases login will succeed. Not so with ODP.NET managed driver. You have to specify tns alias precisely with the properly cased string,
as it was used while adding credential to the Oracle Wallet. Is it a bug or a feature? I guess both.
Also, be careful how you compile, if your target is 64 bit OS, then use x86 or AnyCPU as a target. We were out of luck trying to compile for x64 target, meaning that application must run as a 32-bit process, even on 64-bit OS, otherwise Oracle Wallet won’t work.