SSIS Containing SQL Authentication Runs in IDE but fails in SQL Server Agent Job

I have a SSIS package which gets data from flat file and writes it to SQL. It was running fine in IDE with Windows Authentication in the OLEDB Connection. But I wanted this to be run by anyone instead of using my Windows Authentication setting, so I changed the Authentication to SQL Auth. When I run this package in SQL Job, it fails, but runs fine in IDE. What would be the issue ?

My Package level ProtectionLevel is : EncryptSensitiveWithUserKey

Do I need to change the ProtectionLevel to EncryptSensitiveWithPassword instead?

This is the error I am getting: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

The worst part is, I reverted to run in the Windows Auth itself as of now, but now I am getting a strange error -

Executed as user: . Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:46:00 AM Error: 2019-08-15 10:46:09.43 Code: 0xC0209303 Source: Import_Freight_Data Connection manager "Source Excel (xls) File" Description: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2019-08-15 10:46:09.43 Code: 0xC020801C Source: Get Data From xls File Source - XLS file import [96] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Source Excel (xls) File" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2019-08-15 10:46:09.43 Code: 0xC0047017 Source: Get Data From xls File SSIS.Pipeline Description: Source - XLS file import failed validation and returned error code 0xC020801C. End Error Error: 2019-08-15 10:46:09.43 Code: 0xC004700C Source: Get Data From xls File SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2019-08-15 10:46:09.43 Code: 0xC0024107 Source: Get Data From xls File Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:46:00 AM Finished: 10:46:09 AM Elapsed: 8.906 seconds. The package execution failed. The step failed.

When you use EncryptSensitiveWithUserKey, SSIS will decrypt sensitive values using your windows identity as a means of authentication. When this job runs in SQL agent, it is no longer running under your account and cannot decrypt the password.

You can use EncryptSensitiveWithPassword, but this means that you will need to store the package password in clear text when you call the package from SQL agent. Some more preferable options would be:

• Use windows auth, but run the package under a proxy. More on that here: https://community.spiceworks.com/how_to/129293-configure-an-ssis-package-to-execute-as-a-proxy-account
• Set the password at runtime using package configurations. More on that here: https://docs.microsoft.com/en-us/sql/integration-services/lesson-5-add-ssis-package-configurations-for-the-package-deployment-model?view=sql-server-2017

m