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