Quick summary

We have been working on migrating our library of SSIS packages from SQL Server 2008 hosted on physical Windows Server 2003 to SQL Server 2012 hosted on virtual Windows Server 2012. The new packages we deploy using project deployment to an Integration Services Catalog. The packages import data into another SQL Server 2012 database on a separate server/instance.

It seems we get a "Login failed" error when the package is attempting to execute multiple simultaneous SQL operations against the same SQL Server 2012 target database.

Quick comparison of environments

OLD (where it works)

  • Target database is SQL Server 2008, on physical Windows Server 2003.
  • SSIS 2008 packages executed from file system on Windows Server 2003; receive parameters from machine environment variables.

NEW (where we sometimes get the error)

  • Target database is SQL Server 2012, on virtual Windows Server 2012.
  • SSIS 2012 packages executed from SQL 2012 Integration Services Catalog on separate server: also virtual Windows Server 2012.

The error

An unusual error is occurring for some packages in the new environment:

An OLEDB error has occurred
Error code: 0x80040E4D
An OLEDB record is available.
Source: "Microsoft SQL Server Native Client 11.0"
HResult: 0x80040E4D
Description: "Login failed for user 'MyUser'"

In the target database SQL log, we have this entry:

Source: Logon, Message: Login failed for user 'MyUser'. Reason: Password did not match that for the login provided. [CLIENT: {IP addr. for SSIS DB server}]

While this appears cut and dried, it does not tell the whole story.

What we know

First of all, we double-checked the database connection strings over in the SSIS package. The packages have database connection managers whose connection properties are bound to project parameters, which in turn are bound to environment variables in the Integration Services Catalogs.

We verified that the packages are executed using the correct environment.

Furthermore, SQL operations using the same connection manager work fine leading up to the error!

The common factor in all the errors seems to be that when multiple simultaneous SQL operations occur using the same database connection manager, we get this "login failed" error.

When we refactor a failing portion of a package to execute database operations one at a time instead of simultaneously, the error goes away.

But it worked in our old environment...

These packages worked fine in SSIS 2008 targeting a SQL Server 2008 database, running on physical Windows Server 2003 servers.

...And it still works when we run the packages from Visual Studio 2012

When we run the new SSIS 2012 packages from Visual Studio against the same target database (SQL 2012 on virtual Windows Server 2012), we do not encounter the error.

This leads us to believe it has something to do with how they are deployed and run in the Integration Services Catalog, but we cannot figure out what is wrong. Here is where we could use some help.

  • P.S. The database connection manager has RetainSameConnection set to FALSE. However, I will reiterate that this was not a problem in the old environment, and I am concerned that if the resolution here involves changing this setting to TRUE, that we would lose the performance benefit of having parallel operations in the first place (is this assumption correct...?) Commented May 27, 2015 at 15:20
  • Intriguing. I have an identical issue in data factory/SSIS runtime. Tasks using the same OLE DB connections work.. then some of them don't halfway through execution. Same Login error. In my case I am assigning Password to the connection string by using an expression.
    – Nick.Mc
    Commented Feb 6, 2019 at 7:06
  • I've verified that setting Persist Security Info= True makes no difference to my issue.
    – Nick.Mc
    Commented Feb 7, 2019 at 6:34

4 Answers 4


Here it is: the failing login in question was a SQL Server login. Here is how we configured the SQL connection manager properties for old, new, and developer environments:

  • ServerName - MyServer
  • InitialCatalog - MyDB
  • UserName - MyUser
  • Password - ***********
  • ConnectionString - Data Source=MyServer;User Id=MyUser;Initial Catalog=MyDB;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;

While this worked perfectly on the old SQL 2008 environment, and it was no problem from Visual Studio, it failed during simultaneous database operations from the SQL 2012 Integration Services Catalog, but started working when we added Password=****** to the connection string.

  • ConnectionString - Data Source=MyServer;User Id=MyUser;Password=********;Initial Catalog=MyDB;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;

It would seem that if a second simultaneous connection is being opened while in the SQL 2012 Integration Services Catalog execution environment, only the connection string itself (of the initial connection?) is consulted, not Password or other constituent properties of the connection manager. Therefore, Password has to be in the connection string.

  • I've verified that setting Persist Security Info= True makes no difference to my issue
    – Nick.Mc
    Commented Feb 7, 2019 at 6:37
  • .. and neither does RetainSameConnection
    – Nick.Mc
    Commented Feb 7, 2019 at 7:27

It worked indeed but only once, as it wouldn't save the password even with Persist Security Info=True; I ended up using a Windows user account to connect, and switch the package security to rely on SQL Server roles and it worked.


I've found that the case of the username seems be relevant when scheduling the task to run using a job. The SSIS package will run in VS but give the login error when launched from a job. Check the job history log to see which login name failed. Changing the case of the username on the failing connection (in the SSIS package) to match the case of the user account name as it appears under Security->Logins in SSMS seemed to fix my issue.


Try Setting the ProtectionLevel to "EncryptSensitiveWithPassword" and setting a password in the "PackagePassword" section of the package properties (F4 key). This got me passed the login error for the dtsx package when executing it using Visual Studio.

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.