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


1 Answer 1


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.

  • The PackageProtectionLevel is EncryptAllWithPassword. If I expand the Expressions option, there is nothing. However, I scripted the Job as in the screenshots and was able to see (in plain text) the password for the SQL user that is initiating the connection. if I setup a connection between my Server2008 and Server2000 machine (Connect>Database Engine>SQL Server Authentication) I can successfully connect. However now that I know the password, when I run the package I am only given the option to use windows Authentication. The "Use SQL Server Auth" is greyed out.
    – mcv110
    Commented Apr 22, 2021 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.