SSIS Package in SSDT 2017 fails in SQL Server Job Agent with Proxy Account 'Login Failed for user xxxx....'

Linda Doan 6 Reputation points
2021-06-04T16:01:57.68+00:00

Dear Support,

I have spent the past few months working on this and I've tried everything that is recommended from internet resources and I am still unable to get the following issue resolved.

I need to schedule an SSIS package in Visual Studio (SSDT) 2017 to run and here's what I've done after months of working on this:

1) In SSMS, created an extract using the Data Export Wizard and saved the package to the File System on the local drive
2) In Logins, created login and granted the login sysadmin and public server role, granted db_owner database role, granted SqlAgentRole, SqlagentOperatorRole,SqlagentUserRole to this account
3) Created server credential for this login
4) Created a proxy account for this login
5) Created a server agent job with the owner being my login account (admin)
6) Created the step for this job > setup the type for SQL server integration services package > Run as the login proxy account > package source = file system > for execution options = 32-bit runtime checked
7) in SSDT created a project > open and added the SSIS package > change the Runtime64bit to False > encryption is set to user key

Tested the package in Visual Studio = Works fine > No errors

When execute the job at job step, it errors with:

".... Connection manager "SourceConnectionOLEDB"..... "Microsoft SQL Server Native Client 11.0".... "Login failed for user 'xxxxxxx" SSIS error Code DTS_E_CANNOTCQUIRECONNECTIONFROMCONNECTIONMANAGER..

I've deleted and recreated the account as well as the job many times and redo the SSIS packages without any luck.

Please assist as I am now against tight deadline and need to get this resolved ASAP. Thank you

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,438 questions
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 32,236 Reputation points
    2021-06-07T06:23:56.803+00:00

    Hi @Linda Doan ,

    Have you tried below resolution? If not you may have a try.

    1. Change all the connection managers in the package to use windows authentication.

    Note: This is not an option while communicating with third party datasources which does not support windows authentication like Oracle.

    1. Encrypt the package with "EncryptSensitiveWithPassword" or "EncryptAllWithPassword" and provide a package password each time User wants to edit/manipulate the package.
      1. Create a configuration file to provide the connection information during Package runtime.

    More details please refer: ssis-error-code-dts_e_oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting

    Regards,

    Zoe


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

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October


  2. Olaf Helper 40,246 Reputation points
    2021-06-08T06:23:23.497+00:00

    'Login failed for user xxxxx'

    So you use a SQL login, not Windows credentials? I wonder a bit, because you always wrote you use your account? Is the SQL Server running in mixed mode to allow SQL logins, at all?