question Re: sqoop import from SQL Server with windows authentication in Archives of Support Questions (Read Only)

sqoop import from SQL Server with windows authentication

singhpraveen201 — Thu, 02 Feb 2017 23:18:55 GMT

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.

Re: sqoop import from SQL Server with windows authentication

ssubhas — Fri, 03 Feb 2017 10:27:23 GMT

@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

Re: sqoop import from SQL Server with windows authentication

singhpraveen201 — Mon, 06 Feb 2017 14:01:19 GMT

Thanks @Sindhu, it worked. I missed defining some of the parameters.

Re: sqoop import from SQL Server with windows authentication

rajsyrus — Wed, 08 Feb 2017 14:50:45 GMT

@Sindhu

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

Re: sqoop import from SQL Server with windows authentication

singhpraveen201 — Thu, 09 Feb 2017 02:35:55 GMT

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.

Re: sqoop import from SQL Server with windows authentication

vranganathan — Thu, 09 Feb 2017 03:15:49 GMT

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

Re: sqoop import from SQL Server with windows authentication

rajsyrus — Thu, 09 Feb 2017 17:37:24 GMT

@Praveen Singh

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.

Re: sqoop import from SQL Server with windows authentication

rajsyrus — Thu, 09 Feb 2017 17:43:15 GMT

@Venkat Ranganathan

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

Re: sqoop import from SQL Server with windows authentication

singhpraveen201 — Thu, 09 Feb 2017 17:47:41 GMT

@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.

Re: sqoop import from SQL Server with windows authentication

rajsyrus — Thu, 09 Feb 2017 17:58:06 GMT

@Praveen Singh

I am trying to write the data to user home directory, so no problem with the permission.

Sqoop Command:

Re: sqoop import from SQL Server with windows authentication

singhpraveen201 — Thu, 09 Feb 2017 19:01:33 GMT

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.

Re: sqoop import from SQL Server with windows authentication

rajsyrus — Fri, 10 Feb 2017 16:44:59 GMT

@Praveen Singh

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

Re: sqoop import from SQL Server with windows authentication

SAsInSumit — Mon, 18 Sep 2017 10:01:37 GMT

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

Re: sqoop import from SQL Server with windows authentication

jayasimhareddy9 — Mon, 27 Aug 2018 16:52:24 GMT

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.

Re: sqoop import from SQL Server with windows authentication

produbanarg — Tue, 03 Dec 2019 13:09:06 GMT

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.

Re: sqoop import from SQL Server with windows authentication

CN — Tue, 15 Feb 2022 15:21:01 GMT

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

Any thoughts?

Re: sqoop import from SQL Server with windows authentication

DianaTorres — Tue, 15 Feb 2022 16:00:43 GMT

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.