SSIS package hits "login failed for user" when simultaneous operations occur against the same target database

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 co

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.

StackExchange Database Administrators Q#102570, answer score: 4