The SSIS connection manager has issues in storing the password. Hence we can follow the below-proposed solutions:
Method 1 : Create and use SQL Server Agent Proxy account which uses credential that makes SQL Server Agent capable of the running job as an account having required permissions to run the job or as the account which created the package.
* This method is used for decrypting secrets and key requirements of the user are satisfied.
* There are chances that this method could fail because the secrets are decrypted by taking the current user and current computer information which will inadvertently fail if the project or package is moved to a different computer.
Method 2 : Set the value of Package Protection Level Property to Server Storage. This stores packages on the SQL Server and lets users access this through SQL Server database roles.
Method 3 : Set the value of Package Protection Level property as “Encrypt Sensitive with Password”. The method uses a password for encrypting sensitive information. This can be added to the SQL Server Agent job to run the job effectively.
Method 4 : Package configuration can be used to save sensitive information which can later be stored in a secured folder. When the package is run, the information is obtained from this config file. If we do not want to save the secrets in the package, we can set the property of Protection Level to “DontSaveSensitive”.
Method 5 : Package templates having a good protection level can be created for long-term solutions.
