Created 02-02-2017 03:18 PM
I am trying to import a table from Microsoft SQL Server 11.0.5058 through Sqoop (which is a service on Hortonwork Data Platform) into HDFS. Given the user i have, has only windows authentication (LDAP) on SQL Server. Tried few approaches 1. Kept the sqljdbc4.jar in sqoop shared library and used import command. 2. Downloaded sqljdbc_auth.dll and kept it in java library and tried running import command. But no luck.
Created 02-03-2017 02:27 AM
Sql Server does not support Windows authentication with traditional sqljdbc4.jar on UNIX flavours. Try the following steps and jtds driver:
1) Download the jtds driver from: https://sourceforge.net/projects/jtds/files/ (find the FAQ on jtds at http://jtds.sourceforge.net/faq.html)
2) Copy jtds files to sqoop lib
3) Use the following connection string template to modify according to your environment and connect:
sqoop export --connect "jdbc:jtds:sqlserver://IE11WIN7:1433;useNTLMv2=true;domain=IE11WIN7;databaseName=default_db" --table "test_table_view" --hcatalog-database default --hcatalog-table t1 --columns col2,col3 --connection-manager org.apache.sqoop.manager.SQLServerManager --driver net.sourceforge.jtds.jdbc.Driver --username IEUser --password 'Passw0rd!' --update-mode allowinsert --verbose
Created 02-03-2017 02:27 AM
Sql Server does not support Windows authentication with traditional sqljdbc4.jar on UNIX flavours. Try the following steps and jtds driver:
1) Download the jtds driver from: https://sourceforge.net/projects/jtds/files/ (find the FAQ on jtds at http://jtds.sourceforge.net/faq.html)
2) Copy jtds files to sqoop lib
3) Use the following connection string template to modify according to your environment and connect:
sqoop export --connect "jdbc:jtds:sqlserver://IE11WIN7:1433;useNTLMv2=true;domain=IE11WIN7;databaseName=default_db" --table "test_table_view" --hcatalog-database default --hcatalog-table t1 --columns col2,col3 --connection-manager org.apache.sqoop.manager.SQLServerManager --driver net.sourceforge.jtds.jdbc.Driver --username IEUser --password 'Passw0rd!' --update-mode allowinsert --verbose
Created 02-06-2017 06:01 AM
Thanks @Sindhu, it worked. I missed defining some of the parameters.
Created 02-08-2017 06:50 AM
Will it work with kerberos enabled cluster?, if yes i am using the following query,but still i am facing errors,
sqoop import --connect "jdbc:jtds:sqlserver://XXXXXXXXXXXXXXX;domain=NTXXXX;databaseName=XXXX;ServerSpn=XXXXXXXX@realm;authenticationScheme=JavaKerberos" --table data_table --connection-manager org.apache.sqoop.manager.SQLServerManager --driver net.sourceforge.jtds.jdbc.Driver --target-dir /tmp/111 --split-by AR_ID -m 1 -- --schema dbo --user XXXXXX --password 'XXXXXX' --verbose
Created 02-08-2017 06:35 PM
Rajendra, you need to first generate a kinit ticket for the user you have logged in from. Once it is kerberos authenticated, then you can fire sqoop query.
Created 02-09-2017 09:37 AM
I am generating the ticket and then only i am sqooping. problem is with the delagated token authorization with SQL server i hope, because eval and list-databases are working fine, but map reduced triggered actions like import got failed.
Created 02-08-2017 07:15 PM
MS JDBC driver 4.0 and later allow users to use Java kerberos option with username and password. But this is different from Integrated Authentication which is not supported where you use the kerberos cluster of hadoop to authenticate with Sql Server as there is no kerberos context in containers
Created 02-09-2017 09:43 AM
I am using 4.2 driver only, i tried with both integrated and self authentication method, but no success.may be problem is with the delagated token authorization with SQL server i hope, because eval and list-databases are working fine, but map reduced triggered actions like import got failed
Created 02-09-2017 09:47 AM
@RajendraM Can you check if your for which you are generating Kinit ticket has permission to write in the directory which you are specifying in the sqoop import command. Also if you can share the Sqoop command which you are firing now.
Created 02-09-2017 09:58 AM
I am trying to write the data to user home directory, so no problem with the permission.
Sqoop Command:
sqoop import --connect "jdbc:jtds:sqlserver://XXXXXXXXXXXXXXX;domain=NTXXXX;databaseName=XXXX;ServerSpn=XXXXXXXX@realm;authenticationScheme=JavaKerberos" --table data_table --connection-manager org.apache.sqoop.manager.SQLServerManager --driver net.sourceforge.jtds.jdbc.Driver --target-dir /tmp/111 --split-by AR_ID -m 1 -- --schema dbo --user XXXXXX --password 'XXXXXX' --verbose
Created 02-09-2017 11:01 AM
sqoop import --table <TableName> --connect "jdbc:jtds:sqlserver://<HostName>:<PortNo>;useNTLMv2=true;domain=<DomainName>;databaseName=<DB_Name>" --connection-manager org.apache.sqoop.manager.SQLServerManager --driver net.sourceforge.jtds.jdbc.Driver --username <WindowsUserName> --password <'********'> --verbose --target-dir <TargetDirectory> -m 1 I think useNTLMv2=true may do the trick. Can you try the above query.
Created 02-10-2017 08:44 AM
Thanks..!!, your comment helped in narrowing my problem.
Created 12-03-2019 05:09 AM
Hi @singhpraveen201, we tried this but still the error.
sqoop import --connect "jdbc:jtds:sqlserver://xxxxxxxxxx:49200;useNTLMv2=true;domain=xxxxxx;databaseName=ALMACEN" --table "CARTERA.CONTRATOS_GARRA_DIARIA" --target-dir "/xxxxxx/xxxxxx/temp/sqoop_risk" --split-by FECHA_INFORMACION --connection-manager org.apache.sqoop.manager.SQLServerManager --driver net.sourceforge.jtds.jdbc.Driver --username xxxxxx --password 'xxxxxx' --verbose
19/12/03 10:00:33 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.4
19/12/03 10:00:33 DEBUG tool.BaseSqoopTool: Enabled debug logging.
19/12/03 10:00:33 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
19/12/03 10:00:33 DEBUG sqoop.ConnFactory: Loaded manager factory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
19/12/03 10:00:33 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
19/12/03 10:00:33 INFO manager.SqlManager: Using default fetchSize of 1000
19/12/03 10:00:33 INFO tool.CodeGenTool: Beginning code generation
19/12/03 10:00:33 DEBUG manager.SqlManager: Execute getColumnInfoRawQuery : SELECT t.* FROM [CARTERA.CONTRATOS_GARRA_DIARIA] AS t WHERE 1=0
19/12/03 10:00:33 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.
19/12/03 10:00:34 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
java.sql.SQLException: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:649)
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:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:903)
at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:762)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:785)
at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:288)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:259)
at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:245)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:333)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1858)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1657)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:494)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
19/12/03 10:00:34 ERROR tool.ImportTool: Import failed: java.io.IOException: No columns to generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1663)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:494)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
We use the jtds jar to but we can found a solution. You can help me? Thanks.
Created 09-18-2017 03:01 AM
Here is another option for SQL Server JDBC authentication on my blog if you are looking for commercial support. Full disclaimer: I do get a paycheck from Progress.
jdbc:datadirect:sqlserver://server1:1433;AuthenticationMethod=ntlmjava;Domain=Americas;User=SUMITS;Password=my_windows_auth_pwd
Created 08-27-2018 09:52 AM
Hi @Sindhu , @Praveen Singh
Can you help on below issue,
I'm trying to export data into MSSQL Server Windows authentication from hive ,
i placed JTDS Drivers in Sqoop library,
below is my sqoop command,
sqoop export --connect "jdbc:jtds:sqlserver://<Servername>:1433;useNTLMv2=true;domain=<Domain Name>;databaseName=<DataBase name>" --table "<TableName>" --hcatalog-database <HIVE DB> --hcatalog-table <HIVE Table> --connection-manager org.apache.sqoop.manager.SQLServerManager --driver net.sourceforge.jtds.jdbc.Driver --username <SQL UserName> --password '<SQL Password>' --update-mode allowinsert --verbose
i'm getting the below error:
18/08/27 05:40:33 ERROR tool.ExportTool: Encountered IOException running export job: org.apache.hive.hcatalog.common.HCatException : 2016 : Error operation not supported : Store into a partition with bucket definition from Pig/Mapreduce is not supported
Thanks in advance.
Created 02-15-2022 07:21 AM
Will JTDS support MultiSubnetFailover=true? its not working it seems.
Any thoughts?
Created 02-15-2022 08:00 AM
Hi @CN As this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post.
Regards,
Diana Torres,