Sql-server – How do SSIS connection strings send passwords

connection-stringcredentialssql serverssis

I have a SQL server that pulls data from another SQL server via SSIS. We recently converted the server targeted by SSIS to VM. Since the VM conversion I have been getting "login failed for user '(username)'.". errors and the SSIS package fails to run.

The connection string of the SSIS package indicates the username (which is correct for the target DB), but I do not see how it passes the password to connect to that DB and pull that data. So how does SSIS send passwords if not in the connection string?

The Package Protection Level is set to EncryptAllWithPassword

enter image description here

Windows Authorization only option to run.

SSIS Execute package utility

Best Answer

The Package protection level matters here as a setting of DontSaveSensitive will remove any sensitive data, like a password, when you save the package. You might enter the password, click validate connection and it's good but as soon as you save, it's wiped out and when you re-open, connections are broken because the password is incorrect (blank)

enter image description here

There are challenges with Package Protection Levels other than DontSaveSensitive. You can encrypt all or sensitive data with user key. That is, the author of the package's Active Directory bits are used to transparently encrypt data. Which is great until that person leaves the organization and the AD account is removed. Hard to unlock something when you're a ghost. Encrypt all or sensitive with password is overkill for a single user and a shared secret among all the developers is no secret at all. So, the advice back in the day was Don' Save Sensitive and use some other means of populating sensitive values. Thus...

Check your expressions. If I have an expression on the Password and the expression is the string "1SecretPassword!" don't save sensitive can't wipe the password out because it's an expression, not a stored value. Semantics, but it's true. I advise people to do the expression stuff in SSIS variables and assign them in properties so instead of seeing "1SecretPassword!", in my packages you would see @[User::Password] which has the password in clear text. Yeah, it's a terrible thing for security, so that's why we had the next thing

enter image description here

Configurations! This allowed us to configure a single element per config entity (Environment Variable or Registry key) or point to a cookie jar of settings (SQL Server table or xml based package configuration file). We had a two phase configuration method in place - the Environment Variable would bootstrap the Config connection string which then pointed at a SQL Server table. As long as the xml/table entity exists, the application of configuration values will just consume them all. Which was nice because you could then add/remove config values without changing code.

enter image description here

But sometimes you would get a package through testing and ready for production and realized you forgot to do any of that. You do not want to go back through the whole testing cycle and approval by the high holy SOX compliance review panel that only meets every other week so what do you do, command line overrides when the package executes. This is why I want to see how the job is defined because what you see in the designer/visual studio might be stomped over at run-time by the parameters defined in the job.

enter image description here

There's a fifth package protection level: Server encryption. I honestly forgot how this works but I think when you deploy a package to the MSDB it strips the other protections and replaces it with its own but it's been nearly a decade since I've dealt with package deployment model in capacity.

Related Question