Share via

SSIS connection times out only on server

Pete Koutoulas 1 Reputation point
2021-08-30T13:29:56.897+00:00

I have an SSIS package that runs in my development environment but fails when deployed to SQL Server. The package contains a connection manager which connects to a remote server (not the one on which the package is deployed.) The error is "connection timeout." Both I and a teammate are able to run the package from our respective workstations. But when attempting to run on either of two available servers (one is SQL 2014, the other is SQL 2019), both encounter the error: 

2-UpdateMunisFromCloud:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Login timeout 
expired".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "A network related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. 
Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server 
Books Online.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Named Pipes 
Provider: Could not open a connection to SQL Server [53]. ".

How would I begin to troubleshoot this issue? I feel like the timeout is a red herring and that there is something else going on because it happens consistently on the server and is never encountered when running on a dev workstation. Our workstations are on the same LAN and same Windows domain as the local servers.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 51,346 Reputation points
    2021-08-30T15:05:34.037+00:00

    The underlying error appears to be an issue connecting across the network to the other server. From the server that you are running the SSIS instance from it seems like it is unable to connect to the remote SQL Server perhaps because of firewall or other issues, or perhaps the server is on another network and there isn't a trust across the domains.

    To debug this I would first start by testing that, from the SSIS server, you can successfully connect to the remote SQL server. Note that you should be using the credentials that SSIS will be using. If the connection fails then it is a credentials/network issue. Given the 2 errors you posted that is where the problem seems to be at.

    To test you can use either SSMS on the SSIS server, if it is installed, or create a simple Powershell script to connect using SQL client.

  2. Igor Gelin 21 Reputation points
    2021-08-30T21:07:02.86+00:00

    You may try to enable Named Pipes for SQL Server.

    https://support.gfi.com/hc/en-us/articles/360012847114-Enabling-Named-Pipes-for-SQL

    HTH,

  3. ZoeHui-MSFT 35,556 Reputation points
    2021-08-31T03:01:30.8+00:00

    Hi @Pete Koutoulas ,

    Please check if the remote server has been configured to allow remote connections.

    1.In Object Explorer, right-click a server and select Properties.

    2.Click the Connections node.

    3.Under Remote server connections, select or clear the Allow remote connections to this server check box.

    configure-the-remote-access-server-configuration-option

    For SQL Server connection issues, you may also refer troubleshoot-connecting-to-the-sql-server-database-engine.

    Regards，

    Zoe

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October