Sqoop to SQL Server with Integrated Security

[#1]

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?

[#2]

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

[#3]

Thanks Neeraj - how does this help with integrated security?

[#4]

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/

[#5]

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

[#6]

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

[#7]

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 ?

[#8]

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

[#9]

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.

[#10]

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.