Sqoop from Oracle with SSL

[#1]

Hello -

We are trying to import data from Oracle (12.1.0.2)  using Sqoop and with SSL enabled.

I have tested without encryption and the sqoop command works and we can import data.

However, I am having troubles figuring out the correct syntax to add the SSL options to the Sqoop command. From what i have read online, it requires (at least) these:  useSSL=true    and  requireSSL=true.

I have tried many variations of adding the options to the sqoop command and none work.  I get an error from Sqoop indicating "invalid connection string format" 

Here is the connection string that works:

sqoop import -Dmapred.job.queue.name=hi.adhoc --connect jdbc:oracle:thin:@170.173.150.162:1522/phdssts2 --username p378428 -P --table P378428.BAR --target-dir /user/p378428/insights2/ --fields-terminated-by '\t' --delete-target-dir --verbose

Below are four of variants that don't work.   If anyone knows how to add the SSL options to a sqoop command to Oracle...that would be great.

Thanks...

(a) sqoop import -Dmapred.job.queue.name=hi.adhoc --connect jdbc:oracle:thin:@170.173.150.162:1522/phdssts2 --useSSL=true --requireSSL=true --username p378428 -P --table DSS_STAGE.KHS_ZC_ARRIV_MEANS --target-dir /user/p378428/insights-zc-khs_SSL_1/ --fields-terminated-by '\t' --delete-target-dir --verbose


(b) sqoop import -Dmapred.job.queue.name=hi.adhoc --connect jdbc:oracle:thin:@170.173.150.162:1522/phdssts2;useSSL=true;requireSSL=true --username p378428 -P --table DSS_STAGE.KHS_ZC_ARRIV_MEANS --target-dir /user/p378428/insights-zc-khs_SSL_1/ --fields-terminated-by '\t' --delete-target-dir --verbose

(c) sqoop import -Dmapred.job.queue.name=hi.adhoc --connect jdbc:oracle:thin:@170.173.150.162:1522/phdssts2&useSSL=true&requireSSL=true --username p378428 -P --table DSS_STAGE.KHS_ZC_ARRIV_MEANS --target-dir /user/p378428/insights-zc-khs_SSL_1/ --fields-terminated-by '\t' --delete-target-dir --verbose

(d) sqoop import -Dmapred.job.queue.name=hi.adhoc --connect "jdbc:oracle:thin:@170.173.150.162:1522/phdssts2;useSSL=true;requireSSL=true" --username p378428 -P --table DSS_STAGE.KHS_ZC_ARRIV_MEANS --target-dir /user/p378428/insights-zc-khs_SSL_1/ --fields-terminated-by '\t' --delete-target-dir --verbose

[#2]

Have you taken a look at
http://www.oracle.com/technetwork/topics/wp-oracle-jdbc-thin-ssl-130128.pdf?

The useSSL and requireSSL are keywords used by MySQL JDBC drivers, not by
Oracle JDBC. The syntax required for Oracle is detailed in the white-paper
above, and following that should help you proceed.

In general on the shell, it is better to quote detailed strings with lots
of non-alphabetic characters, such as is done in your (d) example. That's
what would've most likely worked if you were using MySQL in this instance
(given that ; and & are both special in shell).

[#3]

Thanks for the quick reply.

I did find that article, but thought that the SSL options/keywords might be the same.  

I'll revew the article and let you know if this works.

thank you!

...d

[#4]

Unfortunately the JDBC standard's strength ends at its APIs. The connection
string, perhaps aptly named, is merely a string and there's no
feature-based standards around it - every JDBC provider is uniquely
different and using each requires consulting its own documentation.

[#5]

Just wondering....has anyone successfully used Sqoop with SSL to connect to oracle and import data into cloudera cluster / hdfs ?

[#6]

Hi All,

As of now there is no option from Sqoop command to add any tag and it will follow network encryption.

But if you want to apply network encryption then there are some oracle configurations that should set prior making the import.

Kindly set the below parameter at hadoop side.

[root@host-10-17-101-233 ~]# export HADOOP_OPTS="-Doracle.net.crypto_checksum_client=REQUESTED -Doracle.net.crypto_checksum_types_client=SHA1 -Doracle.net.encryption_client=REQUIRED -Doracle.net.encryption_types_client=AES256"

This will add these configurations at container level.

If i run eval command to check out the network then it will show encrypted one.

[root@host-10-17-101-233 ~]# sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select network_service_banner from v\$session_connect_info where sid in (select distinct sid from v\$mystat)"

Warning: /opt/cloudera/parcels/CDH-5.8.3-1.cdh5.8.3.p0.2/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

17/10/23 03:20:31 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.8.3

17/10/23 03:20:31 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

17/10/23 03:20:31 INFO teradata.TeradataManagerFactory: Loaded connector factory for 'Cloudera Connector Powered by Teradata' on version 1.6c5

17/10/23 03:20:32 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.

17/10/23 03:20:32 INFO manager.SqlManager: Using default fetchSize of 1000

17/10/23 03:20:32 INFO manager.OracleManager: Time zone has been set to GMT

------------------------

| NETWORK_SERVICE_BANNER |

------------------------

| SHA1 Crypto-checksumming service adapter for Linux: Version 12.1.0.2.0 - Production |

| Crypto-checksumming service for Linux: Version 12.1.0.2.0 - Production |

| AES256 Encryption service adapter for Linux: Version 12.1.0.2.0 - Production |

| Encryption service for Linux: Version 12.1.0.2.0 - Production |

| TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production |

------------------------

Note:- If i don't set the export parameter then it will show sqoop eval output like this:

sqoop eval --connect "jdbc:oracle:thin:@//host-10-17-100-113.coe.cloudera.com:1521/orcl" --username venkat --password cloudera --query "select network_service_banner from V\$SESSION_CONNECT_INFO where SID = sys_context('USERENV','SID')"

Warning: /opt/cloudera/parcels/CDH-5.8.3-1.cdh5.8.3.p0.2/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

17/10/23 03:09:32 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.8.3

17/10/23 03:09:32 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

17/10/23 03:09:32 INFO teradata.TeradataManagerFactory: Loaded connector factory for 'Cloudera Connector Powered by Teradata' on version 1.6c5

17/10/23 03:09:32 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.

17/10/23 03:09:32 INFO manager.SqlManager: Using default fetchSize of 1000

17/10/23 03:09:41 INFO manager.OracleManager: Time zone has been set to GMT

------------------------

| NETWORK_SERVICE_BANNER |

------------------------

| TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production |

| Encryption service for Linux: Version 12.1.0.2.0 - Production |

| Crypto-checksumming service for Linux: Version 12.1.0.2.0 - Production |

------------------------

Link: - https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/use-network-encryption-and-integrit...

Kindly go through the doc which will explain you more on the network encryption.

You can compare our output of sqoop eval and the oracle doc to confirm about network encryption

Regards

Nitish Khanna

[#7]

So...just for everyone interested....this still doesn't work.

We set the paramater on the sqoop side, ran the eval command and could see the encyrypted notes.

However...while running the sqoop command, we did a packet capture and all of the data/traffic was in plain text, it was not encrypted at all.

Hopefully, there is another way?

[#8]

Hi Dwill,

Kindly run the Sqoop command like this.

sqoop import -D mapreduce.map.java.opts="-Doracle.net.crypto_checksum_client=REQUESTED -Doracle.net.crypto_checksum_types_client=SHA1 -Doracle.net.encryption_client=REQUIRED -Doracle.net.encryption_types_client=AES128" --connect <hostname> --username<> --password<> --table <>

-------- (command contunues).

NOTE:- These encryption properties will work only with Oracle12g only.

Regards

Nitish

[#9]

Hi Nitish...

Thanks for the update and new info.  I will try it out and let you know how things work.

One question...you wrote the this will only work wiht Oracle 12g....but there is no Oracle 12g...is there?  There is 11g and 12c.  We have 12c at our place.

thanks..d

[#10]

Hi Dwill,

Is it worked for you Sqoop import with ssl enabled oracle db ? I got the same kind of requirement to use sqoop import with ssl enabled db and I am trying connecting through oracle wallet but getting network adapter issues.

Could you please provide me the steps if it is working fine for you ?

Thank you.