search

kubo / ruby-oci8

Ruby-oci8 - Oracle interface for ruby
Other
169 stars 75 forks source link

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

Closed davidtcook closed 10 years ago

davidtcook commented 10 years ago

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 commented 10 years ago

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 commented 10 years ago

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 commented 10 years ago

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 commented 10 years ago

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 commented 10 years ago

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 commented 10 years ago

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 commented 10 years ago

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 commented 10 years ago

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 commented 10 years ago

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.