sqoop import from SQL Server with windows authentication

[#1]

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.

[#2]

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.

[#3]

@Praveen Singh

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

[#4]

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.

[#5]

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

[#6]

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.

[#7]

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

Any thoughts?

[#8]

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.