Support Questions
Find answers, ask questions, and share your expertise

sqoop import from SQL Server with windows authentication

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

Accepted Solutions

Re: sqoop import from SQL Server with windows authentication

@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

14 REPLIES 14

Re: sqoop import from SQL Server with windows authentication

@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

Re: sqoop import from SQL Server with windows authentication

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

Re: sqoop import from SQL Server with windows authentication

Expert Contributor

@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

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

Expert Contributor

@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

Rising Star

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

Expert Contributor

@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

@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

Expert Contributor

@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