Created a SSIS package, it is running fine when I run it on premise but when i try to execute it from sql server agent it gives me error for login failed for user, I am using SQL authentication to connect to the Azure database which are working fine on premise but erroring out on Job agent
This the Error I am getting:
Server Execute Package Utility Version 14.0.1000.169 for
64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 10:59:46 AM Error: 2019-06-07 10:59:47.71 Code: 0xC0208452
Source: Data Flow Task ADO NET Destination [2] Description: ADO NET Destination has failed to acquire the connection
{F51F409E-2990-4C40-8DFF-0B078AF481D4} with the following error message: "Login failed for user 'SVCASQLDW'.".
End Error Error: 2019-06-07 10:59:47.71 Code: 0xC0047017
Source: Data Flow Task SSIS.Pipeline Description: ADO NET Destination failed validation and returned error
code 0xC0208452. End Error Error: 2019-06-07 10:59:47.71 Code: 0xC004700C
Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2019-06-07 10:59:47.71
Code: 0xC0024107 Source: Data Flow Task Description:
There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE
(1). Started: 10:59:46 AM Finished: 10:59:47
AM Elapsed: 0.875 seconds. The
package execution failed. The step failed.