Sqoop ORACLE SSL JDBC network adapter could not establish the connection using TCPS protocol

Question

Am trying to import(SQOOP) table data from ORACLE DB to HDFS using SSL connection.

Sqoop command i use is as below,

sqoop import -Djavax.net.ssl.trustStore=/app/Wallets/client_wallet/ewallet.p12 -Djavax.net.ssl.trustStoreType=PKCS12 -Djavax.net.ssl.trustStorePassword=WalletPasswd123 --connect jdbc:oracle:thin:@testssl --username test --password test --table APP_INSTANCE

ERROR:

17/06/30 18:43:12 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
17/06/30 18:43:12 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
17/06/30 18:43:13 DEBUG tool.BaseSqoopTool: Enabled debug logging.
17/06/30 18:43:13 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/06/30 18:43:13 DEBUG sqoop.ConnFactory: Loaded manager factory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
17/06/30 18:43:13 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
17/06/30 18:43:13 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
17/06/30 18:43:13 INFO manager.SqlManager: Using default fetchSize of 1000
17/06/30 18:43:13 INFO tool.CodeGenTool: Beginning code generation
17/06/30 18:43:13 DEBUG manager.SqlManager: Execute getColumnInfoRawQuery : SELECT t.* FROM APP_INSTANCE AS t WHERE 1=0
17/06/30 18:43:13 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.
17/06/30 18:43:14 ERROR manager.SqlManager: Error executing statement: java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)

tnsnames.ora:

testssl =   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = IP )(PORT = 2484))
    (CONNECT_DATA = (SERVER = dedicated)(SERVICE_NAME = ORACL)))

sqlnet.ora:

WALLET_LOCATION=(SOURCE=(METHOD=FILE)  
      (METHOD_DATA=(DIRECTORY=/app/Wallets/client_wallet)))
SQLNET.AUTHENTICATION_SERVICES = (tcps)
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_128_CBC_SHA,SSL_DH_anon_WITH_3DES_EDE_CBC_SHA)
SSL_CLIENT_AUTHENTICATION = FALSE

Try with SQLPLUS:

$ sqlplus64 neon_main/bullet@testssl

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 30 19:23:58 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

This is working fine. But not working with JDBC.

Please point me out where am wrong.?

Nirmala Nirmala · Accepted Answer · 2017-07-07T08:53:21

Since, you are using the TNS Name alias, you should pass the -Doracle.net.tns_admin= explicitly. Also, JDBC thin will not read the file sqlnet.ora. So, these properties need to be explicitly passed as connection level properties.

Check out SSL with JDBC whitepaper for more details.

Sqoop ORACLE SSL JDBC network adapter could not establish the connection using TCPS protocol

1 Answers