Sqoop: Importing from SQL Server throwing “The TCP/IP connection to the host x.x.x.x, port 1433 has failed”

[#1]

On HDP 2.3.2 with Sqoop 1.4.6, I'm trying to import tables from SQL Server 2008.

I'm able to successfully connect to the SQL Server because I can list databases and tables etc.

However, every single time during imports I run into the following error:

Error: java.lang.RuntimeException: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host x.x.x.x, port 1433 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

Again, I am actually able to successfully import from SQL Server, but only after a couple of retries. However, regardless of whether the import succeeded or failed, I always get the error mentioned above and I was wondering what could be causing the problem? It's rather cumbersome to have to keep repeating the imports whenever they fail.

I've already turned off the connection time-out on the SQL Server, and though the connection from the Hadoop cluster and the SQL Server passes through our corporate firewall, our admins tell me that the timeout on the firewall is 3600 seconds. The imports fail before getting anywhere near that mark.

Just an example of one of the sqoop commands I use:

sqoop import \
  -D mapred.task.timeout=0 \
  --connect "jdbc:sqlserver://x.x.x.x:1433;database=CEMHistorical" \
  --table MsgCallRefusal \
  --username hadoop \
  --password-file hdfs:///user/sqoop/.adg.password \
  --hive-import \
  --hive-overwrite \
  --create-hive-table \
  --split-by TimeStamp \
  --hive-table develop.oozie \
  --map-column-hive Call_ID=STRING,Stream_ID=STRING,AgentGlobal_ID=STRING

Update:

After getting in touch with our network team, it seems this is most definitely a network issue. To add context, the Hadoop cluster is on a different VLAN as the SQL Server and it goes through a number of firewalls. To test, I tried importing from a different SQL Server within the same VLAN as the Hadoop cluster and I didn't encounter this exception at all.

[#2]

@Luis Antonio Torres

Thanks for sharing the details while posting your question. I don't see it that often.

I am going to share this from this link

After much searching and talking to many people I have determined that this issue is being caused by the port being blocked. I am still not 100% certain why this error in particular occurred. If I try with an invalid username or password it will successfully tell me that they are invalid. It is just when it comes to making the actual query that the port is restricted. Most likely different ports are used.

I believe next step is to get tcp dump when you see that error to check further. I have seen this behavior during high network traffic.

[#3]

@Neeraj Sabharwal thanks for your response. I checked out the link you shared but it doesn't seem to be the same problem. I'm able to connect to the SQL Server, it's just that I always see that error - particularly in between map tasks. In any case, I'm coordinating with our network guys as well. I can't be sure, but I do believe the SQL Server resides in a different corporate network as the Hadoop cluster. I'll update my post once I get further details.

[#4]

@Luis Antonio Torres Thanks! Having sqlserver in different vlan increases the probability of network issue.

[#5]

@Luis Antonio Torres it can't hurt to try a ping -t (or just ping if you're on a mac) against the SQL Server IP and see if you're getting dropped packets.

[#6]

@Scott Shaw

Pinged the SQL Server and no dropped packets. I can also telnet to port 1433 and nmap also says the port is open. However, I tried it the other way around - I'm unable to ping the Hadoop cluster from the SQL Server. I'm not really sure if this is significant.

[#7]

Try removing the mapred.task.timeout command.

I also assume you're using the correct jdbc driver?

curl -L 'http://download.microsoft.com/download/0/2/A/02AAE597-3865-456C-AE7F-613F99F850A8/sqljdbc_4.0.2206.100_enu.tar.gz'| tar xz. 

[#8]

@Scott Shaw I removed mapred.task.timeout like you suggested, but I'm still getting the same results. As for the jdbc driver, that's the same one that we downloaded and placed in the Sqoop lib. I'm able to connect to the SQL Server with no problems via SQL Server Management Studio on my workstation which are both in the same corporate network, whereas the Hadoop cluster is on a separate network. I actually had to explicity request for our network guys to allow traffic from the cluster into the SQL Server, so I'm starting to suspect there's something in the network that's causing this issue.

[#9]

@Luis Antonio Torres thanks for the update. Please be sure to post the solution when you find it. I'm sure others will find it helpful.

[#10 · Accepted solution]

I never heard back from our network team with regards to firewall logs, but our NameNode's OS got corrupted and had to be reformatted and HDP reinstalled. For some reason we're not encountering this error any longer.

One difference between the original cluster and the new installation is that we had 4 nodes (1 name node and 3 data nodes) which were virtualized in a single server. Now, we're running a single node cluster (HDP 2.3.4) with no virtualization on the server.