Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Sqoop to SQL Server with Integrated Security

Solved Go to solution

Sqoop to SQL Server with Integrated Security

I need to use sqoop on linux to pull data from SQL Server running with integrated security. Can anyone confirm that they have made this work with HDP 2.3.4 and share the steps?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Sqoop to SQL Server with Integrated Security

Expert Contributor

@Justin MillerI too faced the same problem, finally i resolved the issue with the below mention procedure,it may help you i hope.

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

18 REPLIES 18
Highlighted

Re: Sqoop to SQL Server with Integrated Security

@awhitter

See this https://alexeikh.wordpress.com/2012/05/03/using-sqoop-for-moving-data-between-hadoop-and-sql-server/

You can run sqoop in linux and read sql server to get data into Hadoop

Aslo,

https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html

For example, to connect to a SQLServer database, first download the driver from microsoft.com and install it in your Sqoop lib path.

Then run Sqoop. For example:

$ sqoop import --driver com.microsoft.jdbc.sqlserver.SQLServerDriver \
    --connect <connect-string> ...

When connecting to a database using JDBC, you can optionally specify extra JDBC parameters via a property file using the option --connection-param-file. The contents of this file are parsed as standard Java properties and passed into the driver while creating a connection.

[Note>Note
The parameters specified via the optional property file are only applicable to JDBC connections. Any fastpath connectors that use connections other than JDBC will ignore these parameters.
Highlighted

Re: Sqoop to SQL Server with Integrated Security

Thanks Neeraj - how does this help with integrated security?

Highlighted

Re: Sqoop to SQL Server with Integrated Security

New Contributor

It does not address the integrated authentication problem. Has anyone tried this one instead?

https://datamountaineer.com/2016/01/15/spark-jdbc-sql-server-kerberos/

Highlighted

Re: Sqoop to SQL Server with Integrated Security

Mentor
Highlighted

Re: Sqoop to SQL Server with Integrated Security

@awhitter as far as I know this is not possible. You will have to use SQL Authentication. If security is a concern, you can store the password in a encrypted file and call the file in the Sqoop -import command. If you are unable to change the authentication settings on SQL Server due to corporate policies, you may need to dump the tables as files first and import them directly into Hive.

Highlighted

Re: Sqoop to SQL Server with Integrated Security

I am also waiting the same problem with jbdc42.jar and below is the error stack -

16/03/17 15:51:39 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.
java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.
  at org.apache.sqoop.manager.CatalogQueryManager.listTables(CatalogQueryManager.java:118)
  at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
  at org.apache.sqoop.Sqoop.run(Sqoop.java:148)
  at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
  at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)
  at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)
  at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)
  at org.apache.sqoop.Sqoop.main(Sqoop.java:244)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.
  at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2338)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:1929)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:1917)
  at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1061)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:833)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:716)
  at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:841)
  at java.sql.DriverManager.getConnection(DriverManager.java:571)
  at java.sql.DriverManager.getConnection(DriverManager.java:233)
  at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:883)
  at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
  at org.apache.sqoop.manager.CatalogQueryManager.listTables(CatalogQueryManager.java:102)
  ... 7 more

Can some one please help ?

Highlighted

Re: Sqoop to SQL Server with Integrated Security

Sqoop currently does not support SSO. I got an individual use created and now it works. Thanks

Highlighted

Re: Sqoop to SQL Server with Integrated Security

I am running into the same problem. Tried by passing the driver --driver "com.microsoft.sqlserver.jdbc.SQLServerDriver" along with the sqoop command and get this error.

Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:

Can anyone help, if this is resolved.

Highlighted

Re: Sqoop to SQL Server with Integrated Security

I am running into the same problem. Tried by passing the driver --driver "com.microsoft.sqlserver.jdbc.SQLServerDriver" along with the sqoop command and get this error.

Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:

Can anyone help, if this is resolved.

Don't have an account?
Coming from Hortonworks? Activate your account here