Ldap connection not handled

oracle-linux commented 5 years ago

It seems that ldap entries (via sqlnet.ora & ldap.ora) not handled by LOGIN command

hyee commented 5 years ago

I currently have no environment to test the LDAP feature, could you please try following command:

set debug db
login <the # of LDAP a/c>

And see if the url field is expected? It should be jdbc:oracle:thin:@<tns_name> By the way, please make sure env variable TNS_ADMIN is correctly defined, you can verify this by executing !set from the prompt line

oracle-linux commented 5 years ago

HI Hyee

Yes url field is correct and as expected. By the way TNS_ADMIN variable is fine - both tnsping or ldapsearch provide expected result. Besides Ldap connectivity works fine from within "sqlcl". Here are the results

hyee commented 5 years ago

Hi, May I know the error message that you ran into, and could you execute help -j and check if java.home is the location where the JRE in your PC is installed? I'm not sure if Weblogic can setup a LDAP connection to Oracle database so that sqlplus can access the db through LDAP, it would be nice if you have the turtorial.

oracle-linux commented 5 years ago

Hello Sorry didn't find way to upload images. However here are the error messages encountered : (password information hidden) C2N00001> conn sys/****@C1N00002 as sysdba DBC-00872: oracle.net.ns.NetException: could not resolve the connect identifier "C1N00002"; [DB] Start connecting: { bigStringTryClob = "true", defaultRowPrefetch = "3000", driverClassName = "oracle.jdbc.driver.OracleDriver", freeMemoryOnEnterImplicitCache = "true", internal_logon = "sysdba", ["oracle.jdbc.J2EE13Compliant"] = "true", ["oracle.jdbc.TcpNoDelay"] = "true", ["oracle.jdbc.autoCommitSpecCompliant"] = "false", ["oracle.jdbc.convertNcharLiterals"] = "true", ["oracle.jdbc.defaultLobPrefetchSize"] = "2097152", ["oracle.jdbc.mapDateToTimestamp"] = "true", ["oracle.jdbc.maxCachedBufferSize"] = "104857600", ["oracle.jdbc.timezoneAsRegion"] = "false", ["oracle.jdbc.useFetchSizeWithLongColumn"]= "true", ["oracle.jdbc.useNio"] = "true", ["oracle.net.keepAlive"] = "true", ["oracle.net.networkCompression"] = "on", ["oracle.net.ssl_server_dn_match"] = "true", password = "****", processEscapes = "false", url = "jdbc:oracle:thin:@C1N00002", useFetchSizeWithLongColumn = "true", useThreadLocalBufferCache = "true", user = "sys", ["v$session.program"] = "SQL Developer" DBC-00872: oracle.net.ns.NetException: could not resolve the connect identifier "C1N00002"

The help -j command returns (for java.home entry only) java.home = /u01/app/oracle/product/12.2.0.1/jdk/jre

As already stated Sqldeveloper Cmdline works perfetly with such ldap entries (OID based):

sqlcl

SQLcl: Release 12.2.0.1.0 RC on Wed Apr 03 16:09:47 2019

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show version SQLcl version: 12.2.0.1.0 SQL> show jdbc -- Database Info -- Database Product Name: Oracle Database Product Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Database Major Version: 12 Database Minor Version: 2 -- Driver Info -- Driver Name: Oracle JDBC driver Driver Version: 12.2.0.1.0 Driver Major Version: 12 Driver Minor Version: 2 Driver URL: jdbc:oracle:oci8:@ Driver Location: resource: oracle/jdbc/OracleDriver.class jar: /u01/app/oracle/product/12.2.0.1/jdbc/lib/ojdbc8.jar JarSize: 4036257 JarDate: Tue Dec 13 16:39:52 CET 2016 resourceSize: 2604 resourceDate: Tue Dec 13 08:39:48 CET 2016

SQL> connect sys@C1N00002 as sysdba Password? (**?) **** Connected. SQL>

Last information : Target Environment (C1N00002) is a RAC 12.1.02 Source Environnment is cluster 12.2.0.1 (upon Grid 18) $OH/network/admin/sqlnet.ora contains NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT) $OH/network/admin/ldap.ora contains OID server information

Regards

hyee commented 5 years ago

Regarding to the error message, it seems like the program is not able to identify TNS entry C1N00002 in $TNS_ADMIN/tnsnames.ora. If you have sqlplus installed in the environment, please try below commands to check if sqlplus can access the entry after dbcli is launched:

sqlplus
conn sys/""@C1N00002 as sysdba
host tnsping C1N00002

And there are mainly two differences by comparing to sqlcl in this case:

sqlcl uses oci8 driver while dbcli uses thin driver, if this is the case, you can try modifying oracle\oracle.lua and replace thin as oci8 and then retry if it works
If the TNS entry is not defined however sqlcl is able to access, the reason is sqlcl relies on its set option LDAPCON or environment variable $LDAPCON, you could check if the variable is defined
If neither of above work, please try conn / as sysdba

oracle-linux commented 5 years ago

Hello

Thanks and that's Great ! Replacing "thin" by "oci8" was the solution. That means that thin driver cannot leverage LDAP (aka ldap.ora & sqlnet.ora files instead of tnsnames.ora files) Any specific constraint by using such driver ?

Regards

Thanks again

hyee commented 5 years ago

Thanks for your comments. My guess is that sysdba authentication without specifying password needs the Bequeath connection instead of TCP connection, and Bequeath is unsupported in the JDBC thin driver. You can verify this by connecting with non-sysdba account via thin driver.

hyee commented 5 years ago

Any specific constraint by using such driver ?

oci driver relies on Oracle client or Oracle home(including that the version of ojdbc8.jar must match the dynamic libraries of the Oracle home), thin driver doesn't, which means oci is not so portable.

As my test that the performance of oci driver is not better than thin driver in many cases, as well as the CPU usage and stability

dbcli has enabled network compression (introduced since 12c) for thin driver to reduce the network traffic, the option is oracle.net.networkCompression. I'm not sure if this option works for oci, you may need to specify it in sqlnet.ora if you want to use network compression.

hyee / dbcli

Ldap connection not handled #23

sqlcl