Since we activated the Always On mode on our server, we have some issues regarding our SQL jobs (running SSIS package) which failed randomly with this message :
Failed to acquire connection "DB". Connection may not be configured correctly or you may not have the right permissions on this connection.
More details :
- This issue did not happen before the activation of the Always On
- The connection is defined in an OLEDB component
- We have another environment/server with this kind of architecture and there is no issue with it
Trace of the listener :
Message Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 15.0.2000.5 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: 2:18:01 PM Error: 2022-02-15 14:18:30.07 Code: 0xC0202009 Source: TestImport Connection manager "DB_PRIMARY" Description: 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]. ". End Error Error: 2022-02-15 14:18:30.94 Code: 0xC00291EC Source: Execute SQL Task Link coupons movement to collection Execute SQL Task D escription: Failed to acquire connection "DB_PRIMARY". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:18:01 PM Finished: 2:18:30 PM Elapsed: 29.984 seconds. The package execution failed. The step failed.