question Re: Sqoop: Importing from SQL Server throwing “The TCP/IP connection to the host x.x.x.x, port 1433 has failed” in Archives of Support Questions (Read Only)

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

latorres — Mon, 18 Jan 2016 14:46:01 GMT

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.

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

nsabharwal — Mon, 18 Jan 2016 20:04:25 GMT

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

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

SQLShaw — Mon, 18 Jan 2016 22:08:50 GMT

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

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

latorres — Tue, 19 Jan 2016 09:10:40 GMT

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

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

latorres — Tue, 19 Jan 2016 09:17:28 GMT

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

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

nsabharwal — Tue, 19 Jan 2016 09:18:44 GMT

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

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

SQLShaw — Tue, 19 Jan 2016 21:04:22 GMT

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.

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

latorres — Wed, 20 Jan 2016 08:54:07 GMT

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

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

SQLShaw — Wed, 20 Jan 2016 20:45:46 GMT

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

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

latorres — Tue, 26 Jan 2016 16:00:07 GMT

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.

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

aervits — Tue, 02 Feb 2016 23:21:16 GMT

@Luis Antonio Torres has this been resolved? Can you post your solution or accept best answer?

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

latorres — Wed, 03 Feb 2016 08:05:52 GMT

@Artem Ervits yes, this has been resolved but I didn't accept my own answer because we never found out what exactly was wrong. The issue merely stopped occurring after we reinstalled HDP on a server with no virtualization - I detail this in my answer. In any case, I've accepted my own answer as a reference, for the benefit of others.Thanks!

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

subodadithya — Fri, 10 Nov 2017 14:53:30 GMT

@Luis Antonio Torres Listing databases (Sqoop eval) just needs the Namenode access to sql server. Whereas, for import/export (mapreduce tasks) Datanodes also need to have access to the sqlserver. I know your issue is resolved and this is a late reply but someone else might be looking for this in future.