Importing data from MS SQL Server through SCOOP with SSL

kums — Tue, 10 Jul 2018 00:03:00 GMT

The goal here is I need to import data from MS SQL Server database to HDFS. The connectivity between Hadoop Cluster and MS SQL Server works fine. I confirmed this by TELNETing to port 1433. I am also able to --list-tables.

[root@api1.dev ~]# sudo -u XXXXXXX /usr/hdp/current/sqoop-client/bin/sqoop list-tables --connect "jdbc:sqlserver://XX.XX.XXX.XXX:1433;database=XXXXXXXXXX;username=XXXXXXXX;password=XXXXXXXX"
Warning: /usr/hdp/2.6.4.0-91/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/hdp/2.6.4.0-91/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
18/07/09 16:44:43 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.6.4.0-91
18/07/09 16:44:43 INFO manager.SqlManager: Using default fetchSize of 1000
XXXXXXXXXXXXXX<the table name >

DBAs have enabled SSL Encryption on the database side and they have shared the SSL Cert asking us to use when we pull the data out of database.

I did go through this link https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-2017

on JDBC Documentation.

and here is the command that I have arrived

[root@api1.dev ~]# sudo -u XXXXXXXX /usr/hdp/current/sqoop-client/bin/sqoop import --connect "jdbc:sqlserver://XX.XXX.XXXX.XXX:1433;database=XXXXXXXXXX;username=XXXXXXXXXX;password=XXXXXXXX;encrypt=true;trustServerCertificate=false;trustStore=/etc/pki/CA/certs/XXXXXXXXXXXXX.crt" --table XXXXXXXXXX --fields-terminated-by , --escaped-by \\ --enclosed-by '"' --compress -m 1 --target-dir /user/XXXXXXXXXXXX/ --append --hive-drop-import-delims -- --schema dbo --table-hints NOLOCK

Here is the exception that I get

INFO: java.security path: /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-0.b14.el7_4.x86_64/jre/lib/security
Security providers: [SUN version 1.8, SunRsaSign version 1.8, SunEC version 1.8, SunJSSE version 1.8, SunJCE version 1.8, SunJGSS version 1.8, SunSASL version 1.8, XMLDSig version 1.8, SunPCSC version 1.8]
KeyStore provider info: SUN (DSA key/parameter generation; DSA signing; SHA-1, MD5 digests; SecureRandom; X.509 certificates; JKS & DKS keystores; PKIX CertPathValidator; PKIX CertPathBuilder; LDAP, Collection CertStores, JavaPolicy Policy; JavaLoginConfig Configuration)
java.ext.dirs: /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-0.b14.el7_4.x86_64/jre/lib/ext:/usr/java/packages/lib/ext
18/07/09 16:50:26 ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Invalid keystore format". ClientConnectionId:daf3f972-6029-4629-8817-7bb8ac260c5c
com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Invalid keystore format". ClientConnectionId:daf3f972-6029-4629-8817-7bb8ac260c5c
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1668)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1323)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:902)
	at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
	at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:763)
	at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786)
	at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289)
	at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
	at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:246)
	at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:328)
	at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1853)
	at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1653)
	at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488)
	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615)
	at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
	at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
	at org.apache.sqoop.Sqoop.main(Sqoop.java:243)
Caused by: java.io.IOException: Invalid keystore format
	at sun.security.provider.JavaKeyStore.engineLoad(JavaKeyStore.java:658)
	at sun.security.provider.JavaKeyStore$JKS.engineLoad(JavaKeyStore.java:56)
	at sun.security.provider.KeyStoreDelegator.engineLoad(KeyStoreDelegator.java:224)
	at sun.security.provider.JavaKeyStore$DualFormatJKS.engineLoad(JavaKeyStore.java:70)
	at java.security.KeyStore.load(KeyStore.java:1445)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1525)
	... 25 more

After few more reading it is said that the key needs to be converted into jks format

Has anybody been in this situation ?

Re: Importing data from MS SQL Server through SCOOP with SSL

vmurakami — Thu, 12 Jul 2018 22:47:29 GMT

Hello @Kumar Veerappan!
In this case you need to pass a Java keystore file instead of a cert file. If you only have this cert file, then you can generate you own JKS file and import your cert into your jks.
Could you try to run the following commands?

keytool -genkey -alias myjks -keystore mssql.jks -storepass anypwd
keytool -delete -alias myjks -keystore mssql.jks -storepass anypwd
keytool -import -alias myjks -file /etc/pki/CA/certs/XXXXXXXXXXXXX.crt -keypass anypwd -keystore mssql.jks -storepass anypwd
sudo -u XXXXXXXX /usr/hdp/current/sqoop-client/bin/sqoop import --connect "jdbc:sqlserver://XX.XXX.XXXX.XXX:1433;database=XXXXXXXXXX;username=XXXXXXXXXX;password=XXXXXXXX;encrypt=true;trustServerCertificate=false;trustStore=/mssql.jks" --table XXXXXXXXXX --fields-terminated-by , --escaped-by \\ --enclosed-by '"' --compress -m 1 --target-dir /user/XXXXXXXXXXXX/ --append --hive-drop-import-delims -- --schema dbo --table-hints NOLOCK

Hope this helps!

Re: Importing data from MS SQL Server through SCOOP with SSL

kums — Sat, 14 Jul 2018 01:47:56 GMT

@Vinicus Higa Murakami...it worked I just had to do one more extra step....IN my case I converted my *.crt file into *.pem file and then I used this *.pem file to generate jks.

Thanks

Kumar

Re: Importing data from MS SQL Server through SCOOP with SSL

vmurakami — Sat, 14 Jul 2018 03:30:11 GMT

Good to know @Kumar Veerappan! 🙂

question Importing data from MS SQL Server through SCOOP with SSL in Archives of Support Questions (Read Only)

Importing data from MS SQL Server through SCOOP with SSL

Re: Importing data from MS SQL Server through SCOOP with SSL

Re: Importing data from MS SQL Server through SCOOP with SSL

Re: Importing data from MS SQL Server through SCOOP with SSL