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:

DTS_E_OLEDBERROR
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...?) – Zach Blocker May 27 '15 at 15:20

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.

  • Zach, Can you provide more information on how you actually added the password to the connection string? Did you put the password in raw text? As in: Password=mypassword or did you use the *******? – user78187 Oct 13 '15 at 22:47
  • I'd be curious to see if it would work if you removed the Password & also removed "Persist Security Info" (or set it to false) – Kenneth Fisher Jan 21 '16 at 21:32

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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