SSIS cannot connect to SQL Server using TCP, every other app can

by Daniel Hutmacher   Last Updated September 04, 2017 18:06 PM

I have an SSIS package in a SQL Server 2016 Integration Services Catalog on my server, SERVER1 (2016 SP1 CU4). Among other things, this package connects to SERVER2 (SQL Server 2008 SP2). The package recently started failing with the following error:

Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: No connection could be made because the target machine actively refused it."

What I've done/tried:

  • I can connect from SERVER1 to SERVER2 using osql.exe and Management Studio and I can also telnet to port 1433 on SERVER2.
  • I've tried changing the server name in the SSIS connection manager from SERVER2 to tcp:SERVER2 as well as using just the IP address.
  • SERVER2 uses port 1433
  • I'm using SQL Server authentication
  • The execution outcome is the same whether I run the package in the Integration Services Catalog (right-clicking the .dtsx, "Execute"), run it as an Agent job step, or run it from a command prompt with the dtexec utility.
  • There are no enabled outbound firewall rules on SERVER1.
  • SERVER1 has IPv6 disabled.
  • No clustering and no AG on any of the machines.
  • On SERVER1, I've explicitly disabled "Named pipes" in Configuration Manager -> Client Protocols -> Native Client 11.0
  • I also added SERVER2 to the hosts file on SERVER1

I don't have administrative permissions and can't log in interactively on SERVER2, so I can't check firewall settings, etc on that machine.

Using netstat on SERVER1 when the job runs, I can see that there's a process trying to connect to SERVER2 on port 1433 (with status TIME_WAIT) but it never appears to connect.

My client recently added another named instance to SERVER1 and changed the TCP ports of the default instance, but he says he hasn't changed anything on SERVER2.

Related Questions

Database Connection Failed

Updated July 10, 2020 06:06 AM

c++, sqlserver express, native client connection

Updated October 08, 2019 13:06 PM

How do I get the Query Timing with sqlcmd?

Updated May 23, 2017 04:06 AM