Sqoop with sql server driver, eval and list-tables are working fine , but import failing with below error,

[#1]

Query: sqoop import --connect "jdbc:sqlserver://XXXXXXXXXXXXXXXXXXXXXXX;instance=XXXX;database=XXXXX;ServerSpn=MSSQLSvc/XXXX@realm;integratedSecurity=true;authenticationScheme=JavaKerberos" --table DATA_TABLE --target-dir /tmp/111 --split-by AR_ID -m 1 -- --schema dbo

INFO mapreduce.Job: map 0% reduce 0% 17/02/07 11:30:52 INFO mapreduce.Job: Task Id : attempt_1486380022013_0343_m_000000_0, Status : FAILED

Error: java.lang.RuntimeException: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:9b11416e-fdd5-4cb4-93e9-4c6e20401cd8 at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:167) at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:76) at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:136) at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:749) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162) Caused by: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:9b11416e-fdd5-4cb4-93e9-4c6e20401cd8 at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:220) at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:165) ... 9 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:9b11416e-fdd5-4cb4-93e9-4c6e20401cd8 at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1748) at com.microsoft.sqlserver.jdbc.KerbAuthentication.intAuthInit(KerbAuthentication.java:175) at com.microsoft.sqlserver.jdbc.KerbAuthentication.GenerateClientContext(KerbAuthentication.java:278) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2860) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2360) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2346) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1404) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1068) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:904) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:451) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1014) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:270) at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:300) at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:213) ... 10 more Caused by: java.security.PrivilegedActionException: GSSException: No valid credentials provided (Mechanism level: Failed to find any Kerberos tgt) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at com.microsoft.sqlserver.jdbc.KerbAuthentication.getClientCredential(KerbAuthentication.java:198) at com.microsoft.sqlserver.jdbc.KerbAuthentication.intAuthInit(KerbAuthentication.java:149) ... 26 more Caused by: GSSException: No valid credentials provided (Mechanism level: Failed to find any Kerberos tgt) at sun.security.jgss.krb5.Krb5InitCredential.getInstance(Krb5InitCredential.java:147) at sun.security.jgss.krb5.Krb5MechFactory.getCredentialElement(Krb5MechFactory.java:122) at sun.security.jgss.GSSManagerImpl.getCredentialElement(GSSManagerImpl.java:193) at sun.security.jgss.GSSCredentialImpl.add(GSSCredentialImpl.java:427) at sun.security.jgss.GSSCredentialImpl.<init>(GSSCredentialImpl.java:62) at sun.security.jgss.GSSManagerImpl.createCredential(GSSManagerImpl.java:154) at com.microsoft.sqlserver.jdbc.KerbAuthentication$1.run(KerbAuthentication.java:188) at com.microsoft.sqlserver.jdbc.KerbAuthentication$1.run(KerbAuthentication.java:186) ... 30 more Container killed by the ApplicationMaster. Container killed on request. Exit code is 143 Container exited with a non-zero exit code 143

[#2]

Are you using sqljdbc42.jar or jtds.jar for the driver ? Also, is sqoop eval working with integratedSecurity option or you are passing user id & password ?

[#3]

@anatva

yes i am using sqljdbc42.jar. and i tried with both sql and jtds.jar driver class. sqoop eval working fine with integratedSecurity and userid & password too.

[#4]

> IntegratedSecurity=true;

This is not supported - your eval etc will work because they are done from the local java process but there is no kerberos credential context in the container when the mappers run to do the import work

[#5]

@Venkat Ranganathan

Even my opnion is same , eval picking the kerberos from the local host. But how to resolve for import?

[#6]

Are these relevant:

https://community.hortonworks.com/questions/52137/sqlserver-sqoop-import-integrated-security-kerbero...

https://community.hortonworks.com/questions/7433/how-to-impot-job-on-sqoop-from-sql-server-connecti....

[#7 · Accepted solution]

Finaly i resolved the issue...!!!

Sqoop SQL Server data import to HDFS worked with manual parametric the authentication(using windows credential) with added parameter on the SQL Server JDBC driver, as integrated security is not supported by the SQL driver as of now due to the Kerberos authentication(Delegated tokens distributed over cluster while running MR job).

So we need to pass the windows authentication with password and with the integrated security disabled mode to import the data to the system. As normal SQL server driver does not support, so I had used the jtds.jar and the different driver class to pull the data to the Hadoop Lake.

Sample Command I tried on the server as follows,

sqoop import --table Table1 --connect "jdbc:jtds:sqlserver://<Hostname>:<Port>;useNTLMv2=true;domain=<WindowsDomainName>;databaseName=XXXXXXXXXXXXX" \

--connection-manager org.apache.sqoop.manager.SQLServerManager --driver net.sourceforge.jtds.jdbc.Driver --username XXXXX --password 'XXXXXXX' \

--verbose --target-dir /tmp/33 -m 1 -- --schema dbo

[#8]

Sqoop AD authentication has been resolved using jTDS driver.