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 · Accepted solution]

@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 

[#3]

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

[#4]

@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

[#5]

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.

[#6]

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

[#7]

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

[#8]

@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

[#9]

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

[#10]

@Praveen Singh

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

[#11]

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.

[#12]

@Praveen Singh

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

[#13]

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.

[#14]

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

[#15]

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.

[#16]

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

Any thoughts?

[#17]

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.