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

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

Expert Contributor

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

1 ACCEPTED SOLUTION

Expert Contributor

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

View solution in original post

7 REPLIES 7

Cloudera Employee

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 ?

Expert Contributor

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

Rising Star

> 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

Expert Contributor
@Venkat Ranganathan

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

Expert Contributor

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

New Contributor

Sqoop AD authentication has been resolved using jTDS driver.

; ;