April 17, 2018 at 11:14 am
I have a data connection that uses SQL Server Authentication to connect to a remote server in my job. When I deploy this job to my instance of SQL Server and review validation, I see errors,
Error: Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information.
For the protection level, I am using "EncryptSensitiveWithPassword". When deploying my package, I am 99.999% sure I entered that password correctly at least one of the times out of dozens I've tried deploying this. Also, other data connections using Windows Authentication still work. When I run the package on the server, go to connection managers, and change the related connection string to include the password, it works fine.
Am I doing something wrong, or will I need to add that password in the connection managers interface every time I deploy the package?
April 17, 2018 at 2:29 pm
See the following: https://docs.microsoft.com/en-us/sql/integration-services/security/access-control-for-sensitive-data-in-packages?view=sql-server-2017
You also have to provide the password when executing the package, otherwise it will fail.
April 28, 2018 at 12:50 am
Assuming you are on SQL server 2012 and above, you will have to set password at project level as well as at package level. One option that worked for me was I directly deployed into SSIS catalog using project model from TFS , at that point it should ask you password to decrypt and save in SSIS catalog. Then just point the job to use this package from the SSIS catalog and you should be all set.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply