This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
An ETL job schdule is failing to run. I am trying to bring data from server outside my domain. the admin on server LMN which is outside my domain created a login credentials (user efg)
when I run the job manually its successful. but on shcedule, it fails. here is the error I am getting.
Message Executed as user: OTH\abc. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 4:40:01 PM Error: 2023-03-13 16:40:02.56 Code: 0xC0202009 Source: FECLoad Connection manager "BAH DC_Data" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'efg'.". End Error Error: 2023-03-13 16:40:02.57 Code: 0xC020801C Source: Load FEC BAH to STG FEC BAH DC_Location_Info [2] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "BAH DC_Data" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2023-03-13 16:40:02.57 Code: 0xC0047017 Source: Load FEC BAH to STG SSIS.Pipeline Description: FEC BAH DC_Location_Info failed validation and returned error code 0xC020801C. End Error Error: 2023-03-13 16:40:02.57 Code: 0xC004700C Source: Load FEC BAH to STG SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2023-03-13 16:40:02.57 Code: 0xC0024107 Source: Load FEC BAH to STG Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:40:01 PM Finished: 4:40:02 PM Elapsed: 1.422 seconds. The package execution failed. The step failed.
Login failed for user 'efg'
As the error message says.
Can you connect to that SQL Server using SSMS with the same credentials?
Hi @Olaf Helper
I appreciate your reply.
Can you connect to that SQL Server using SSMS with the same credentials?
Yes, i can connect using SSMS, I connect using IP address of server LMN (I can't connect by using server hostname). I use the credential of the user efg to do that
Hi @Mohammed Abdul Majeed Khan,
Please set up a proxy account and then use the account to run the agent job for a try.
A detailed tutorial you may take a reference to:
Regards，
Zoe Hui
If the answer is helpful, please click "Accept Answer" and upvote it.
Hi @ZoeHui-MSFT
Thank you for your reply.
in this project I am working with 2 sources of data:
The 1st server, STU, is in domain OTH. and i am already using a proxy account with the credentials of user abc. The connection and pulling of data is successful from this server.
the second user efg is not in the domain. this is a new data source added to the project later.
so how can i set up 2 separate proxy accounts to be used in my scenario? or can i use user efg credentials in the same proxy account that has user abc credentials?
illustration
Hi @Mohammed Abdul Majeed Khan,
Here is a same thread about the issue, it mentioned the PackageProtectionLevel.
Check if it is helpful.
Hi @ZoeHui-MSFT I made a proxy account and followed all the steps in your suggested blog but still job fails. The error is the following::
Message Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 4). The step failed.