Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

sqoop import from SQL Server with windows authentication

avatar
Contributor

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.

1 ACCEPTED SOLUTION

avatar

@Praveen Singh

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 

View solution in original post

16 REPLIES 16

avatar
Contributor

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.

avatar
Expert Contributor

@Praveen Singh

Thanks..!!, your comment helped in narrowing my problem.

avatar
New Contributor

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.

avatar
Explorer

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

avatar

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.

avatar
New Contributor

Will JTDS support MultiSubnetFailover=true? its not working it seems. 

 

Any thoughts?

avatar
Community Manager

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,
Community Moderator


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community: