SSIS Error Code Dts_e_oledberror an OLE DB error has occurred error code 0x80040e4d

Question

When trying to execute ssis package through sql server agent job, I get the error message 'SSIS Error Code Dts_e_oledberror an OLE DB error has occurred error code 0x80040e4d' Description: Login failed for user 'username'

The user which is used to run the agent job has system administration access and the package executes successfully when its is executed as ssis package, what would the issue be?

Thank you in advance!

Answer 1

Login failed for user 'username'

A clear error message, that SQL login don't exists in SQL Server or logon is denied for the SQL login or don't have access permission for the requested database.

Answer 2

Hi @sql dev ,

To resolve the issue, use following methods:

Method 1: Use a SQL Server Agent proxy account. Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage. Change the SSIS Package ProtectionLevel property to ServerStorage. This setting stores the package in a SQL Server database and allows for access control through SQL Server database roles.

Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword. Change the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword. This setting uses a password for encryption. You can then modify the SQL Server Agent job step command line to include this password.

Details please refer to ssis-package-doesnt-run-when-called-job-step

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Answer 3

@ZoeHui-MSFT I have tried all of the stated option and the job fails with the same error message - e.g. when trying to use a proxy account to run the job it looks as though its still using the sql server agent service account to execute the job (the service account which the sql agent service is under - in Services)

Is there anything that I may be missing?

Answer 4

Same problem here. Did you manage to figure out how to solve the problem?