2

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.

ElRoro
  • 203
  • 1
  • 13

1 Answers1

0

When enabling AlwaysOn, there is only one active database ("master") and synchronous or asynchornous copies that are call replicas ("slave") that you can't access to.

The SQL Agent operates on local database, that can be in the active or slave mode. When active, your job will operate. When slave your job will fail.

So is is necessary to ask is the database is active prior to all execution of SQL server Agent job.

I wrote some UDF to test if the database is reachable or not that you can have at :

And also to reproduce the SQL Server jobs on all replicas... !

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • Thanks for your answer and I understand what you say but on my slave the SQL Server Agent is disable so I mean, I don't see how a job can run on the slave. And if it's the case, how can we force to always run on the primary ? – ElRoro Feb 15 '22 at 13:34
  • How can you say this is may slave and this is my master without testing with a query? Especially when you have synchronous replica with automatic failover... – SQLpro Feb 15 '22 at 16:13
  • Because the issue is not when I execute a query, the issue happened randomly during the initialization of the connection in the SSIS package. I give to my SSIS package a configuration file with the parameters of the connection (the "primary" ip, username and password). If the issue is that it tries to connect to slave with these parameters, of course an issue will be raised. Maybe I miss something... – ElRoro Feb 16 '22 at 09:20
  • Why don't you use a listener in the package ? Also a failover during the SQL Agent process will fail... – SQLpro Feb 16 '22 at 12:00