SSL connection - ORA-12538: TNS:no such protocol adapter

[davidtcook]

Environment :
Solaris 10, ruby 1.9.1, ruby-oci8 2.1.6, Oracle client 11.2.0.3.0, connecting to databases also on 11.2.0.3.0.
Database connection details/aliases are in tnsnames.ora, username/password details are stored in an Oracle Wallet (i.e. ewallet.p12, cwallet.sso). Environment variables TNS_ADMIN and WALLET_DIR are set correctly.

The situation :

1. All connections (non-SSL and SSL) can be made successfully using "sqlplus" from the command-line using Oracle Wallet entries or explicit username/password
2. Connections via ruby-oci8 to non-SSL DBs can be made successfully using the same Wallet as in 1, or with explicit username/password.
3. Connections via ruby-oci8 to SSL DBs using either Oracle Wallet or specified username/password fail with this message :
   OracleInterface::CategoryClassMethods#full_path: ORA-12538: TNS:no such protocol adapter

Minimal program to reproduce the issue (connection string redacted) - if I replace the connection string with "ABC123_USER" (where that references a Wallet entry /tnsnames.ora entry for a non-SSL database), this program works :

require 'dbi'
require 'oci8'

begin
dbenv = OCI8.new(nil, nil, "XYZ123_SSL_USER")
rescue OCIException => e
puts "OracleInterface::CategoryClassMethods#full_path: #{e}"
end

Note that both command-lines work:
sqlplus /@ABC123_USER
sqlplus /@XYZ123_SSL_USER

In summary: This problem only occurs when using ruby-oci8 to attempt to connect to a DB over an SSL connection.
Please let me know if you need any further information to help track down this issue.

[kubo]

Could you post the output of the following command or send it to kubo@jiubao.org?

1. Check out https://github.com/kubo/ocidump.git by git command or download https://github.com/kubo/ocidump/archive/master.zip and unzip it.
2. Change ORACLE_INC, CC, CFLAGS, LD_SHARED and LDFLAGS in the Makefile of the downloaded source code.
3. Run make command.
4. Run the following command and post or send the output.

LD_PRELOAD=./libocidump.so sqlplus /@XYZ123_SSL_USER

Delete sensitive data from the output or set the environment variable OCIDUMP_HIDE_STRING=1 in advance to hide string data.

[davidtcook]

Thanks for the quick reply - won't be able to check until I'm back at work on Monday, I'll let you know the results then !

David.

[davidtcook]

Update: Managed to compile the libocidump library (used the Solaris settings in the Makefile, and needed to add the "-m64" flag to CFLAGS and LD_SHARED, my Oracle client library is 64-bit).

Ran it as follows :
LD_PRELOAD=./libocidump.so sqlplus /@XYZ123_SSL_USER > out1 2>&1

And the output (I did a comparison run against a non-SSL connection, do you want that output too ?) :

1: OCIEnvCreate([0x1001933c0], 0x6, (nil), (nil), (nil), (nil), 0, (nil)) => OCI_SUCCESS
1: OCIHandleAlloc(0x1001933c0, [0x1001d9370], OCI_HTYPE_ERROR, 0, (nil)) => OCI_SUCCESS
1: OCIHandleAlloc(0x1001933c0, [0x1001da508], OCI_HTYPE_ERROR, 0, (nil)) => OCI_SUCCESS
1: OCIHandleAlloc(0x1001933c0, [0x1001db460], OCI_HTYPE_SERVER, 0, (nil)) => OCI_SUCCESS
1: OCIHandleAlloc(0x1001933c0, [0x1001da428], OCI_HTYPE_SVCCTX, 0, (nil)) => OCI_SUCCESS
1: OCIHandleAlloc(0x1001933c0, [0x1001e0f40], OCI_HTYPE_SESSION, 0, (nil)) => OCI_SUCCESS
1: OCIHandleAlloc(0x1001933c0, [0x1001e30d0], OCI_HTYPE_SESSION, 0, (nil)) => OCI_SUCCESS

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 12 11:19:40 2014

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

1: OCIAttrGet(0x1001db460, OCI_HTYPE_SERVER, [0], (nil), OCI_ATTR_SERVER_STATUS, 0x1001d9370) => OCI_SUCCESS
1: OCIServerDetach(0x1001db460, 0x1001d9370, 0) => OCI_SUCCESS
1: OCIServerAttach(0x1001db460, 0x1001d9370, "XYZ123_SSL_USER", 12, 0) => OCI_SUCCESS
1: OCIAttrGet(0x1001db460, OCI_HTYPE_SERVER, [(nil)], [0], OCI_ATTR_ACCESS_BANNER, 0x1001d9370) => OCI_SUCCESS
1: OCIAttrSet(0x1001da428, OCI_HTYPE_SVCCTX, 0x1001db460, 0, OCI_ATTR_SERVER, 0x1001d9370) => OCI_SUCCESS
1: OCIAttrSet(0x1001e0f40, OCI_HTYPE_SESSION, "", 0, OCI_ATTR_EDITION, 0x1001d9370) => OCI_SUCCESS
1: OCIAttrSet(0x1001e0f40, OCI_HTYPE_SESSION, "/", 1, OCI_ATTR_USERNAME, 0x1001d9370) => OCI_SUCCESS
1: OCIAttrSet(0x1001e0f40, OCI_HTYPE_SESSION, "", 0, OCI_ATTR_PASSWORD, 0x1001d9370) => OCI_SUCCESS
1: OCIAttrSet(0x1001da428, OCI_HTYPE_SVCCTX, 0x1001e0f40, 0, OCI_ATTR_SESSION, 0x1001d9370) => OCI_SUCCESS
1: OCIAttrSet(0x1001e0f40, OCI_HTYPE_SESSION, "SQL*PLUS", 8, OCI_ATTR_DRIVER_NAME, 0x1001d9370) => OCI_SUCCESS
1: OCISessionBegin(0x1001da428, 0x1001d9370, 0x1001e0f40, OCI_CRED_EXT, 0) => OCI_SUCCESS
1: OCIAttrGet(0x1001e0f40, OCI_HTYPE_SESSION, [(nil)], [0], OCI_ATTR_AUDIT_BANNER, 0x1001d9370) => OCI_SUCCESS
1: OCIServerRelease(0x1001da428, 0x1001d9370, "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\nWith the Partitioning, Real Application Clusters and Automatic Storage Management options", 500, OCI_HTYPE_SVCCTX, [0xb200300(11.2.0.3.0)]) => OCI_SUCCESS
1: OCIStmtPrepare2(0x1001da428, [0x100208c60], 0x1001d9370, "SELECT USER FROM DUAL\0", 22, (nil), 0, OCI_NTV_SYNTAX, 32) => OCI_SUCCESS
1: OCIStmtExecute(0x1001da428, 0x100208c60, 0x1001d9370, 0, 0, (nil), (nil), 0x0) => OCI_SUCCESS
1: OCIDefineByPos(0x100208c60, [0x100208910], 0x1001d9370, 1, 0x100107398, 31, SQLT_STR, (nil), 0xffffffff7ffff088, (nil), 0x0) => OCI_SUCCESS
1: OCIStmtFetch(0x100208c60, 0x1001d9370, 1, OCI_FETCH_NEXT, 0) => OCI_SUCCESS
1: OCIAttrGet(0x100208c60, OCI_HTYPE_STMT, [1], (nil), OCI_ATTR_ROW_COUNT, 0x1001d9370) => OCI_SUCCESS
1: OCIStmtFetch(0x100208c60, 0x1001d9370, 1, OCI_FETCH_NEXT, 0) => OCI_NO_DATA
1: OCIAttrGet(0x100208c60, OCI_HTYPE_STMT, [1], (nil), OCI_ATTR_ROW_COUNT, 0x1001d9370) => OCI_SUCCESS
1: OCIStmtRelease(0x100208c60, 0x1001d9370, (nil), 0, 0) => OCI_SUCCESS
1: OCIStmtPrepare2(0x1001da428, [0x100208c60], 0x1001d9370, "BEGIN DBMS_OUTPUT.DISABLE; END;", 31, (nil), 0, OCI_NTV_SYNTAX, 32) => OCI_SUCCESS
1: OCIStmtExecute(0x1001da428, 0x100208c60, 0x1001d9370, 1, 0, (nil), (nil), 0x0) => OCI_SUCCESS
1: OCIAttrGet(0x100208c60, OCI_HTYPE_STMT, [34(PL/SQL EXECUTE)], (nil), OCI_ATTR_SQLFNCODE, 0x1001da508) => OCI_SUCCESS
1: OCIAttrGet(0x1001e0f40, OCI_HTYPE_SESSION, [0x10020b110], (nil), OCI_ATTR_SESSLANG, 0x1001d9370) => OCI_SUCCESS
1: OCIStmtRelease(0x100208c60, 0x1001d9370, (nil), 0, 0) => OCI_SUCCESS
1: OCIAttrGet(0x1001e0f40, OCI_HTYPE_SESSION, [0x10020b110], (nil), OCI_ATTR_SESSLANG, 0x1001d9370) => OCI_SUCCESS
1: OCIStmtPrepare2(0x1001da428, [0x100208c60], 0x1001d9370, "SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (USER LIKE USERID)\0", 154, (nil), 0, OCI_NTV_SYNTAX, 32) => OCI_SUCCESS
1: OCIStmtExecute(0x1001da428, 0x100208c60, 0x1001d9370, 0, 0, (nil), (nil), 0x0) => OCI_SUCCESS
1: OCIDefineByPos(0x100208c60, [0x100206d80], 0x1001d9370, 1, 0xffffffff7fffe730, 250, SQLT_STR, 0xffffffff7fffe82c, 0xffffffff7fffe82a, (nil), 0x0) => OCI_SUCCESS
1: OCIDefineByPos(0x100208c60, [0x100206200], 0x1001d9370, 2, 0xffffffff7fffe838, 250, SQLT_STR, 0xffffffff7fffe934, 0xffffffff7fffe932, (nil), 0x0) => OCI_SUCCESS
1: OCIDefineByPos(0x100208c60, [0x100206060], 0x1001d9370, 3, 0xffffffff7fffe940, 250, SQLT_STR, 0xffffffff7fffea3c, 0xffffffff7fffea3a, (nil), 0x0) => OCI_SUCCESS
1: OCIDefineByPos(0x100208c60, [0x100205ec0], 0x1001d9370, 4, 0xffffffff7fffea48, 250, SQLT_STR, 0xffffffff7fffeb44, 0xffffffff7fffeb42, (nil), 0x0) => OCI_SUCCESS
1: OCIDefineByPos(0x100208c60, [0x100205d20], 0x1001d9370, 5, 0xffffffff7fffeb50, 250, SQLT_STR, 0xffffffff7fffec4c, 0xffffffff7fffec4a, (nil), 0x0) => OCI_SUCCESS
1: OCIStmtFetch(0x100208c60, 0x1001d9370, 1, OCI_FETCH_NEXT, 0) => OCI_NO_DATA
1: OCIAttrGet(0x100208c60, OCI_HTYPE_STMT, [0], (nil), OCI_ATTR_ROW_COUNT, 0x1001d9370) => OCI_SUCCESS
1: OCIStmtRelease(0x100208c60, 0x1001d9370, (nil), 0, 0) => OCI_SUCCESS
1: OCIStmtPrepare2(0x1001da428, [0x100208c60], 0x1001d9370, "SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND   ((USER LIKE USERID) OR (USERID = 'PUBLIC')) AND   (UPPER(ATTRIBUTE) = 'ROLES')\0", 177, (nil), 0, OCI_NTV_SYNTAX, 32) => OCI_SUCCESS
1: OCIStmtExecute(0x1001da428, 0x100208c60, 0x1001d9370, 0, 0, (nil), (nil), 0x0) => OCI_SUCCESS
1: OCIDefineByPos(0x100208c60, [0x100206e98], 0x1001d9370, 1, 0xffffffff7fffed74, 250, SQLT_STR, (nil), 0xffffffff7fffee88, (nil), 0x0) => OCI_SUCCESS
1: OCIStmtFetch(0x100208c60, 0x1001d9370, 1, OCI_FETCH_NEXT, 0) => OCI_NO_DATA
1: OCIAttrGet(0x100208c60, OCI_HTYPE_STMT, [0], (nil), OCI_ATTR_ROW_COUNT, 0x1001d9370) => OCI_SUCCESS
1: OCIStmtRelease(0x100208c60, 0x1001d9370, (nil), 0, 0) => OCI_SUCCESS
1: OCIStmtPrepare2(0x1001da428, [0x100208c60], 0x1001d9370, "BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;\0", 54, (nil), 0, OCI_NTV_SYNTAX, 32) => OCI_SUCCESS
1: OCIBindByPos(0x100208c60, [0x1002089b8], 0x1001d9370, 1, 0x10010ecf0, 8, SQLT_CHR, 0x100111700, 0x100111704, (nil), 0, (nil), 0x0) => OCI_SUCCESS
1: OCIStmtExecute(0x1001da428, 0x100208c60, 0x1001d9370, 1, 0, (nil), (nil), 0x0) => OCI_SUCCESS
1: OCIStmtRelease(0x100208c60, 0x1001d9370, (nil), 0, 0) => OCI_SUCCESS
1: OCIStmtPrepare2(0x1001da428, [0x100208c60], 0x1001d9370, "SELECT DECODE('A','A','1','2') FROM DUAL", 40, (nil), 0, OCI_NTV_SYNTAX, 32) => OCI_SUCCESS
1: OCIDefineByPos(0x100208c60, [0x100208a10], 0x1001d9370, 1, 0xffffffff7ffff078, 22, SQLT_NUM, 0xffffffff7fffeeca, 0xffffffff7fffeec8, (nil), 0x0) => OCI_SUCCESS
1: OCIStmtExecute(0x1001da428, 0x100208c60, 0x1001d9370, 1, 0, (nil), (nil), 0x0) => OCI_SUCCESS
1: OCIStmtRelease(0x100208c60, 0x1001d9370, (nil), 0, 0) => OCI_SUCCESS
1: OCITransCommit(0x1001da428, 0x1001d9370, 0x0) => OCI_SUCCESS
1: OCITransCommit(0x1001da428, 0x1001d9370, 0x0) => OCI_SUCCESS

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options

SQL> 1: OCISessionEnd(0x1001da428, 0x1001d9370, 0x1001e0f40, 0) => OCI_SUCCESS
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
1: OCIServerDetach(0x1001db460, 0x1001d9370, 0) => OCI_SUCCESS
1: OCIHandleFree(0x1001d9370, OCI_HTYPE_ERROR) => OCI_SUCCESS
1: OCIHandleFree(0x1001da508, OCI_HTYPE_ERROR) => OCI_SUCCESS
1: OCIHandleFree(0x1001933c0, OCI_HTYPE_ENV) => OCI_SUCCESS

[kubo]

Thank you!

Could you change ext/oci8/oci8.c at line 550 as follows?

    chker2(OCIAttrSet(svcctx->base.hp.ptr, OCI_HTYPE_SVCCTX,  /* move these 4 lines before OCISessionBegin() */
                      svcctx->usrhp, 0, OCI_ATTR_SESSION,     /* ditto. */
                      oci8_errhp),                            /* ditto. */
           &svcctx->base);                                    /* ditto. */
    chker2(OCISessionBegin_nb(svcctx, svcctx->base.hp.ptr, oci8_errhp,
                              svcctx->usrhp, FIX2UINT(cred),
                              FIX2UINT(mode)),
           &svcctx->base);

If you install ruby-oci8 as a gem, edit RUBY_INSTALL_DIRECTORY/lib/ruby/gems/1.9.1/gems/ruby-oci8-2.1.6/ext/oci8/oci8.c, run make command at the directory and copy oci8lib_191.so to ../../lib.

If it doesn't fix the issue, change lib/oci8/oci8.rb at line 140 as follows and check again.

      # logon by the OCI function OCISessionBegin().
      allocate_handles()
      @session_handle.send(:attr_set_string, 288, "")  # add this line. 288: OCI_ATTR_EDITION
      @session_handle.send(:attr_set_string, OCI_ATTR_USERNAME, username || "/")  # change this line.
      @session_handle.send(:attr_set_string, OCI_ATTR_PASSWORD, password || "")  # ditto.
      if @@oracle_client_version >= ORAVER_11_1
        # 'rubyoci8' is displayed in V$SESSION_CONNECT_INFO.CLIENT_DRIVER

[davidtcook]

Hi, I have applied the changes as specified above (see below for diffs/details), unfortunately I'm still getting the ORA-12538 error on the SSL connection :
OracleInterface::CategoryClassMethods#full_path: ORA-12538: TNS:no such protocol adapter

Do you need to see the (redacted) versions of tnsnames.ora entries that I'm using ?

Details of changes :

~/.gem/gems/ruby-oci8-2.1.6/ext/oci8 $ diff -C 2 oci8_orig.c oci8.c
*** oci8_orig.c Tue May 13 11:33:10 2014
--- oci8.c      Tue May 13 11:33:33 2014
***************
*** 547,554 ****

      /* begin session */
-     chker2(OCISessionBegin_nb(svcctx, svcctx->base.hp.ptr, oci8_errhp,
-                               svcctx->usrhp, FIX2UINT(cred),
-                               FIX2UINT(mode)),
-            &svcctx->base);
      chker2(OCIAttrSet(svcctx->base.hp.ptr, OCI_HTYPE_SVCCTX,
                        svcctx->usrhp, 0, OCI_ATTR_SESSION,
--- 547,550 ----
***************
*** 555,558 ****
--- 551,558 ----
                        oci8_errhp),
             &svcctx->base);
+     chker2(OCISessionBegin_nb(svcctx, svcctx->base.hp.ptr, oci8_errhp,
+                               svcctx->usrhp, FIX2UINT(cred),
+                               FIX2UINT(mode)),
+            &svcctx->base);
      svcctx->state |= OCI8_STATE_SESSION_BEGIN_WAS_CALLED;
      return Qnil;

~/.gem/gems/ruby-oci8-2.1.6/ext/oci8 $ make
gcc -I. -I/app/scm/include/ruby-1.9.1/sparc-solaris2.10 -I/app/scm/include/ruby-1.9.1/ruby/backward -I/app/scm/include/ruby-1.9.1 -I. -DRUBY_EXTCONF_H=\"extconf.h\"  -D_FILE_OFFSET_BITS=64  -fPIC -O3 -g -Wall -Wno-unused-parameter -Wno-parentheses -Wpointer-arith -Wwrite-strings -Wno-long-long -I/app/oraclient/product/11.2.0/rdbms/public -Wall  -o oci8.o -c oci8.c
gcc -shared -o oci8lib_191.so oci8lib.o env.o error.o oci8.o ocihandle.o connection_pool.o stmt.o bind.o metadata.o attr.o lob.o oradate.o ocinumber.o ocidatetime.o object.o apiwrap.o encoding.o oranumber_util.o thread_util.o -L. -L/app/scm/lib -L.    -L/app/oraclient/product/11.2.0/lib32 -R/app/oraclient/product/11.2.0/lib32 -lclntsh -lpthread -lrt -lsocket -ldl -lcrypt -lm   -lc

 ~/.gem/gems/ruby-oci8-2.1.6/lib/oci8 $ diff oci8_orig.rb oci8.rb 
140,141c140,142
<       @session_handle.send(:attr_set_string, OCI_ATTR_USERNAME, username) if username
<       @session_handle.send(:attr_set_string, OCI_ATTR_PASSWORD, password) if password
---
>       @session_handle.send(:attr_set_string, 288, "")  # add this line. 288: OCI_ATTR_EDITION
>       @session_handle.send(:attr_set_string, OCI_ATTR_USERNAME, username || "/")  # change this line.
>       @session_handle.send(:attr_set_string, OCI_ATTR_PASSWORD, password || "")  # ditto.

[kubo]

I’d like to confirm just in case； did you copy oci8lib_191.so to ~/.gem/gems/ruby-oci8-2.1.6/lib after running make command?

Do you need to see the (redacted) versions of tnsnames.ora entries that I'm using?

Thanks. Please post it.

Yet another cause what I think of is that the 32-bit Oracle client library may not support SSL.
Could you recompile ruby and ruby-oci8 as 64-bit executable?
Ruby is compiled as 64-bit if CFLAGS=-m64 is set before running configure script.

[kubo]

Could you download ociconnect.c, compile it as both 32-bit and 64-bit and run them as follows?

gcc -m32 -o ociconnect32 ociconnect.c -I/app/oraclient/product/11.2.0/rdbms/public -L/app/oraclient/product/11.2.0/lib32 -R/app/oraclient/product/11.2.0/lib32 -lclntsh
gcc -m64 -o ociconnect64 ociconnect.c -I/app/oraclient/product/11.2.0/rdbms/public -L/app/oraclient/product/11.2.0/lib -R/app/oraclient/product/11.2.0/lib -lclntsh
./ociconnect32
./ociconnect64

[davidtcook]

Yet another cause what I think of is that the 32-bit Oracle client library many not support SSL.

Hi - it looks like this is it !
I have another server with the 32-bit-only Oracle client installed, and I have another DB that has listeners set up for both non-SSL and SSL, and this was the result from tnsping :

$ tnsping YYYYYYY
TNS Ping Utility for Solaris: Version 11.2.0.2.0 - Production on 14-MAY-2014 10:30:01
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=xxxxxxxxxxxxx)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=YYYYYYY)))
OK (140 msec)

$ tnsping YYYYYYY_SSL
TNS Ping Utility for Solaris: Version 11.2.0.2.0 - Production on 14-MAY-2014 10:30:03
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCPS) (HOST=xxxxxxxxxxxxxx)(PORT=2484))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=YYYYYYY)) (SECURITY=(SSL_SERVER_CERT_DN=CN=_______________________________)))
TNS-12538: TNS:no such protocol adapter

$ file $( which tnsping )
/app/oracle/product/11.2.0.2/bin/tnsping:       ELF 32-bit MSB executable SPARC32PLUS Version 1, V8+ Required, dynamically linked, not stripped

I'm now working on the re-build of Ruby as 64-bit, will try it and let you know.
Thanks for all your help !

[davidtcook]

Whew. "re-build of Ruby as 64-bit" sounds so simple, but on Solaris with a bunch of dependent libraries, it took a while.

Anyway, the good news is - that worked. Running ruby-oci8 with ruby 2.1.2 all built as 64-bit and linked to the Oracle client 64-bit libraries, I can connect to non-SSL and SSL databases now.
Thanks again for all the help !
Marking issue as closed.