question Re: Sqoop with sql server driver, eval and list-tables are working fine , but import failing with below error, in Archives of Support Questions (Read Only)

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

rajsyrus — Tue, 07 Feb 2017 18:39:11 GMT

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

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

anatva — Tue, 07 Feb 2017 23:58:50 GMT

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 ?

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

vranganathan — Thu, 09 Feb 2017 02:16:30 GMT

> 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

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

rajsyrus — Thu, 09 Feb 2017 18:03:16 GMT

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

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

rajsyrus — Thu, 09 Feb 2017 18:07:29 GMT

@Venkat Ranganathan

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

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

bandalora — Fri, 10 Feb 2017 02:48:46 GMT

Are these relevant:

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

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

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

rajsyrus — Fri, 10 Feb 2017 16:40:58 GMT

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

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

balucr — Sat, 29 May 2021 21:39:25 GMT

Sqoop AD authentication has been resolved using jTDS driver.