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-201...
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 ?
