Sqoop & SQL Server # 2 - Configuring Sqoop to work with SQL Server

If you've been using SQL Server for many years you'll be used to having all the tools you need to hand packaged together by Microsoft.

The Big Data world isn't so easy to navigate. Often solutions are constructed from different software which needs to be configured to work together. This is true for Sqoop as well.

In these posts I will use a Cloudera VM which has many tools already setup saving hours of administration. However, there are still some elements that need configuration.

To get the free Cloudera Quickstart VM follow this link. I used the version for Oracle VirtualBox which takes less than 5 minutes to import and then you off an running.

JDBC Configuration

Download The Driver from the VM 

• Navigate to https://www.microsoft.com/en-gb/download/details.aspx?id=11774
• Click Download
• Select the sqljdbc_4.0.2206.100_enu.tar.gz file
• Select next
• Select save file

You can also download the driver on the Host machine and FTP the file into the VM. For instructions see this link.

Extract and copy to location

• Open a terminal session and assume super user permissions by typing

su

• You will be asked for a password followed. The password "cloudera". You now have admin permissions on the VM
• We need to change the working directory. Type

cd /home/cloudera/Downloads

• This is case sensitive so beware the capital D on Downloads
• We now need to extract the contents of the download. Type the following don’t forget the dot in front of the /

tar -zxvf ./sqljdbc_4.0.2206.100_enu.tar.gz

• Navigate to the location of the driver by typing
• 
  

cd ./sqljdbc_4.0/enu

• Copy the driver to the location required by typing

cp sqljdbc4.jar /usr/lib/sqoop/lib

At this point a restart is required so that the new driver can be picked up by the OS

Authentication 

Mixed Mode Authentication needs to be enabled on the SQL Server

• In management studio connect to the server
• Right click on server name and select properties
• Select security tab
• In the top ‘Server authentication’ section, ensure the ‘SQL Server and Windows Authentication mode’ is selected
• The server will need to restart before these changes take effect

Security

The SQL Authenticated account used to access the data must have db_datareader permissions in the source database.