Oracle and IBM MDM and the ORA-28040: No matching authentication protocol

Working through my IBM MDM AE 11.5.0.5 build, against Oracle 12c R2, when I hit this: -

cd /opt/ibm/MDM/AE/mds/scripts
./madconfig.sh Configure_MasterDataManagement

...

Running ODBC SQL statement [select 1 from dual;]...
Executing /opt/ibm/MDM/AE/mds/bin/madsql
/opt/ibm/MDM/AE/mds/bin/madsql: STATE=HY000, CODE=28040, MSG=[InitiateSystems][ODBC Oracle Wire Protocol driver][Oracle]ORA-28040: No matching authentication protocol
Result: 1
Return Code: 1, Time elapsed: 0.099 sec

BUILD FAILED
/opt/ibm/MDM/AE/mds/scripts/build-utils-separation.xml:4563: The following error occurred while executing this line:
/opt/ibm/MDM/AE/mds/scripts/build-utils-separation.xml:99: The following error occurred while executing this line:
/opt/ibm/MDM/AE/mds/scripts/build-utils-separation.xml:1221: The following error occurred while executing this line:
/opt/ibm/MDM/AE/mds/scripts/build-utils-config.xml:1214: ERROR: Unable to connect to the IBM InfoSphere MDM database via ODBC.

Total time: 1 minute 6 seconds
...

so I check the logs: -

ls -al /opt/ibm/MDM/AE/mds/log

total 16
drwxr-xr-x.  2 wasadmin wasadmins  104 Aug  9 13:36 .
drwxr-xr-x. 14 wasadmin wasadmins 4096 Aug  9 13:13 ..
-rw-r--r--.  1 wasadmin wasadmins  160 Aug  9 13:36 mad_ant_register_odbc.log
-rw-r--r--.  1 wasadmin wasadmins  213 Aug  9 13:32 madsql-20180809-133249.mlg
-rw-r--r--.  1 wasadmin wasadmins  213 Aug  9 13:36 madsql-20180809-133641.mlg

cat  /opt/ibm/MDM/AE/mds/log/mad_ant_register_odbc.log

…

Creating the ODBC.INI configuration file '/opt/ibm/MDM/AE/mds/conf/odbc.ini'..

…

cat  /opt/ibm/MDM/AE/mds/log/madsql-20180809-133641.mlg

…

13:36:41 /opt/ibm/MDM/AE/mds/bin/madsql INFO  Version 11.5.0.X, Built: Oct  9 2015 19:10:11
13:36:41 /opt/ibm/MDM/AE/mds/bin/madsql INFO  /opt/ibm/MDM/AE/mds/bin/madsql -nohead -quiet -sqlstmt select 1 from dual;
…

none of which is particularly revealing.

I even check the generated ODBC.INI file: -

cat /opt/ibm/MDM/AE/mds/conf/odbc.ini

[ODBC]
IANAAppCodePage=4
InstallDir=/opt/ibm/MDM/AE/mds
Trace=0
TraceDll=/opt/ibm/MDM/AE/mds/lib/o6trc27.so
TraceFile=odbctrace.out
UseCursorLib=0

[orcl_E001]
ApplicationUsingThreads=1
ArraySize=256000
CatalogIncludesSynonyms=0
CatalogOptions=0
DefaultLongDataBuffLen=1024
EnableScrollableCursors=0
LockTimeOut=-1
UseCurrentSchema=1
SupportSQLBigInt=1
ColumnsAsChar=1
ColumnSizeAsCharacter=1
Driver=/opt/ibm/MDM/AE/mds/lib/o6ora27.so
HostName=oracle.uk.ibm.com
PortNumber=1521
SID=orcl
CachedCursorLimit=50
CachedDescLimit=0
ServerType=2
WireProtocolMode=2

and then test using the MDM SQL tool ( madsql ): -

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/ibm/MDM/AE/linux/lib/
export MAD_ROOTDIR=/opt/ibm/MDM/AE/mds
export MAD_DBTYPE=oracle
export MAD_CONNSTR="DSN=orcl_E001;UID=mdm2oracle;PWD=passw0rd"
export MAD_CTXLIB=ODBC

/opt/ibm/MDM/AE/mds/bin/madsql

/opt/ibm/MDM/AE/mds/bin/madsql: STATE=HY000, CODE=28040, MSG=[InitiateSystems][ODBC Oracle Wire Protocol driver][Oracle]ORA-28040: No matching authentication protocol

Something I read online suggests that I need to add this: -

SQLNET.ALLOWED_LOGON_VERSION=8

to: -

/home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

on the Oracle box.

This I do, which moves me onto this: -

Operation failed.
/opt/ibm/MDM/AE/mds/bin/madsql: STATE=28000, CODE=1017, MSG=[InitiateSystems][ODBC Oracle Wire Protocol driver][Oracle]ORA-01017: invalid username/password; logon denied

Just to be on the safe side, I install the unixODBC tool: -

/usr/bin/yum --noplugins install -y unixODBC

which provides the isql utility, and I set up the configuration: -

vi /etc/odbcinst.ini 

[Oracle]
Description = Oracle ODBC Connection
Driver = /home/wasadmin/app/wasadmin/product/12.2.0/client_1/libsqora.so.12.1
Setup =
FileUsage =

vi /etc/odbc.ini 

[Oracle]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = //oracle.uk.ibm.com:1521/orcl
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
UserID = mdm2oracle

but isql also fails to play ball: -

isql -v Oracle

[28000][unixODBC][Oracle][ODBC][Ora]ORA-01005: null password given; logon denied

etc.

I tinker further: -

 isql -v Oracle mdm2oracle passw0rd

and get this: -

[S1000][unixODBC][Oracle][ODBC][Ora]ORA-28000: the account is locked

[ISQL]ERROR: Could not SQLConnect

which is more useful.

I test using the Oracle sqlplus tool: -

sqlplus mdm2oracle/passw0rd@//oracle.uk.ibm.com:1521/orcl

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 9 15:44:20 2018

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

ERROR:
ORA-28000: the account is locked

which is VERY revealing.

I checked Oracle: -

sqlplus / as sysdba

select username,account_status from dba_users where username = 'MDM2ORACLE';

As expected, this reported: -

...
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
MDM2ORACLE
LOCKED(TIMED)
…

Thus I unlocked the account AND ensured that it could be used for logon: -

alter user MDM2ORACLE account unlock;
grant connect, resource to MDM2ORACLE;

and tried again.

This time BOTH madsql and isql worked: -

/opt/ibm/MDM/AE/mds/bin/madsql

ODBC connection to Oracle via o6ora27.so Version 07.15.0229 (B0235, U0160) (ODBC 03.52.0000) successful.

Enter SQL Stmt: 

SELECT BANNER FROM v$version;
Stmt[1] = [SELECT BANNER FROM v$version]
BANNER                                                                           
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production     
PL/SQL Release 12.2.0.1.0 - Production                                           
CORE 12.2.0.1.0 Production                                                       
TNS for Linux: Version 12.2.0.1.0 - Production                                   
NLSRTL Version 12.2.0.1.0 - Production                                           
Operation committed. (elapsed = 1 seconds)
Enter SQL Stmt: 

and: -

isql ORACLE mdm2oracle passw0rd

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT BANNER FROM v$version;
+---------------------------------------------------------------------------------+
| BANNER                                                                          |
+---------------------------------------------------------------------------------+
| Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production    |
| PL/SQL Release 12.2.0.1.0 - Production                                          |
| CORE 12.2.0.1.0 Production                                                      |
| TNS for Linux: Version 12.2.0.1.0 - Production                                  |
| NLSRTL Version 12.2.0.1.0 - Production                                          |
+---------------------------------------------------------------------------------+
SQLRowCount returns -1
5 rows fetched
SQL> 

Right, now back to madconfig.sh …..