We're using Visual Studio 15.7.2 and using SSIS on SQL 2017 (14.0.3029.16).
We built a connection manager to a 3rd-party, vendor managed SQL Server 2008 R2 (10.50.6529.0). Our only allowed connections to this server are via SQL Login, so Windows Authentication is a no-go.
We can run the package inside of Visual Studio/SSDT just fine, the package executes and completes successfully as we'd expect.
When we open the package in SSDT we're met with error immediately:
Upon looking at Connection Managers we're met with the following icon:
When we open the connection manager, this is what we see (password blank, should be *********):
The package is configured to run (via job) at 8:45 pm Eastern and it fails every time. We get a plethora of errors that basically state we can't connect to the 3rd-party server. Investigation shows that the password isn't being saved so we get denied at the front door!
Has anyone ran into this before? What options do I have to rectify? I'm somewhat new to SSIS, so I'm learning a lot of hard lessons fast.