ALL SQL SERVER JOB ERROR

Question

I'm receiving the error below. I tried to resolve the issue of changing connections properties from my user (sa) to Windows NT options inside the job and removing expired certificates (second error pointed).

I have not changed de server name or sa password.

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

02/04/2025 23:43:15,BACKUP.DIFERENCIAL,Error,,NVSSRVSQL01PV,BACKUP.DIFERENCIAL,,,The job failed. The Job was invoked by User NVSTEC\Administrador. The last step to run was step 1 (DIFERENCIAL).,00:00:02,0,0,,,,0

02/04/2025 23:43:15,BACKUP.DIFERENCIAL,Error,1,NVSSRVSQL01PV,BACKUP.DIFERENCIAL,DIFERENCIAL,,Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2000.8 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 23:43:15 Error: 2025-02-04 23:43:17.84 Code: 0xC00291EC Source: {8681192D-7FA7-4FF3-B660-C14F0969A083} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

End Error Warning: 2025-02-04 23:43:17.84 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning

Error: 2025-02-04 23:43:17.87 Code: 0xC0024104 Source: Back Up Database Task Description: The Execute method on the task returned error code 0x80131904 (A connection was successfully established with the server<c/> but then an error occurred during the login process. (provider: SSL Provider<c/> error: 0 - The certificate chain was issued by an authority that is not trusted.)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2025-02-04 23:43:17.87 Code: 0x80019002 Source: DIFERENCIAL Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2025-02-04 23:43:17.88 Code: 0xC0024104 Source: {55D087A8-322B-402D-B77D-2A81D0D305BD} Description: The Execute method on the task returned error code 0x80131904 (A connection was successfully established with the server<c/> but then an error occurred during the login process. (provider: SSL Provider<c/> error: 0 - The certificate chain was issued by an authority that is not trusted.)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2025-02-04 23:43:17.88 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 23:43:15 Finished: 23:43:17 Elapsed: 2.203 seconds. The package execution failed. The step failed.,00:00:02,0,0,,,,0

0

Answer 1

Hi Santhiago Bueno,

Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

It seems a permission error.

First please ensure that the Local server connection has been configured correctly.

And then please check the account which you are used to run the agent job has the full permission to access the Local server connection.

It is highly recommended to run the package via proxy account in agent job.

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Regards，

Zoe Hui

---

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

Answer 2

I disagree with Zoe. I don't think this is a permissions error. In all this verbiage, I find:

The certificate chain was issued by an authority that is not trusted.

This occurs when you connect with Encrypt=Mandatory or Strict, and no matching certificate is found in the Trusted Certificates store.

In recent years, Microsoft have changed the default for the encryption option in all their client APIs, so that they now default to Mandatory instead of Optional.

The best way is to install a proper certificate for SQL Server and make sure that clients has this certificate, or a root certificate for that cert, in their trusted certificate store.

Since this seems to be a local connection on the server, there is not really any risk for man-in-the-middle attacks, and you can change the connection string to have Trust Server Certificate=true or Encrypt=Optional.