Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: The Cloudera Community will undergo maintenance on Saturday, August 17 at 12:00am PDT. See more info here.

sqoop list-databases error

SOLVED Go to solution

sqoop list-databases error

Expert Contributor

desabv@xxxxx:~#> sqoop list-databases --connect "jdbc:jtds:sqlserver://xxxxx.com:1433" --connection-manager org.apache.sqoop.manager.SQLServerManager --driver net.sourceforge.jtds.jdbc.Driver --username desabv -P
Warning: /sso/sfw/cloudera/parcels/CDH-5.14.0-1.cdh5.14.0.p0.24/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
18/08/02 15:03:27 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.0
Enter password:
18/08/02 15:03:34 INFO manager.SqlManager: Using default fetchSize of 1000
18/08/02 15:03:35 ERROR manager.CatalogQueryManager: Failed to list databases
java.sql.SQLException: I/O Error: DB server closed connection.
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2481)
at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:371)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)

 

Any help anyone 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: sqoop list-databases error

Expert Contributor

The solution would be to use an updated client library that supports SSL. The one you download from the site does not support connecting to an encrypted MS SQL DB

 

 

So for others who try to Sqoop to MS SQL server , this is what i used. Download the updated one from here . https://sourceforge.net/p/jtds/bugs/725/ 

 

sqoop import --connect "jdbc:jtds:sqlserver://xxx.xxxx.xxx.xxx:1433;databaseName=abcd;domain=xxx.xxx.xxx;useNTLMv2=true;integratedSecu..." --driver net.sourceforge.jtds.jdbc.Driver --connection-manager org.apache.sqoop.manager.SQLServerManager --username "desind" -P --fields-terminated-by "\001" --query " 
select Number_, Active FROM dwh_14a.Currency WHERE \$CONDITIONS" --split-by 'Number_' --target-dir /tmp/test --fetch-size 10000 -m1 

3 REPLIES 3

Re: sqoop list-databases error

Champion

Based on the error i think the SQL Server uses an SSL connection and a valid client certificate is required to communicate with the SQL Server.Contact your sql admin for this . Once you have the certificate the error should go off . 

Re: sqoop list-databases error

Expert Contributor

Yes I think you are right. 

 

Logs from SQL Server

 

Date 8/7/2018 2:26:24 PM Log SQL Server (Current - 8/7/2018 2:26:00 PM) Source Logon Message Encryption is required to connect to this server but the client library does not support encryption; the connection has been closed. Please upgrade your client library. [CLIENT: 149.173.90.36]

 

So is there a client library that supports encryption  for JTDS?

Highlighted

Re: sqoop list-databases error

Expert Contributor

The solution would be to use an updated client library that supports SSL. The one you download from the site does not support connecting to an encrypted MS SQL DB

 

 

So for others who try to Sqoop to MS SQL server , this is what i used. Download the updated one from here . https://sourceforge.net/p/jtds/bugs/725/ 

 

sqoop import --connect "jdbc:jtds:sqlserver://xxx.xxxx.xxx.xxx:1433;databaseName=abcd;domain=xxx.xxx.xxx;useNTLMv2=true;integratedSecu..." --driver net.sourceforge.jtds.jdbc.Driver --connection-manager org.apache.sqoop.manager.SQLServerManager --username "desind" -P --fields-terminated-by "\001" --query " 
select Number_, Active FROM dwh_14a.Currency WHERE \$CONDITIONS" --split-by 'Number_' --target-dir /tmp/test --fetch-size 10000 -m1