Sql-server – SSIS package hits “login failed for user” when simultaneous operations occur against the same target database

sql-server-2012ssisssis-2012virtualisation

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.

Best Answer

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.

Related Solutions

Sql-server – Relating ExecutionInstanceGUID to the SSISDB

Too much for a comment, but trying something. From the msdn page of that system table catalog.executions I get:

execution_id - bigint - The unique identifier (ID) for the instance of execution.

From this article - SSIS 2012 – View Connection Manager Information for Past Executions - I understand that:

SSIS 2012 provides a new system variable, ServerExecutionID, for your use inside packages, so if you do any custom logging/notifications it is a good variable to include as it will be a direct pointer to the catalog views that we’ll use to find connection string information. ... Catalog.executions contains one row per execution. This is where we’ll filter by execution_id.

With a sample query of:

DECLARE @execution_id BIGINT = 41753; -- Your execution_id/ServerExecutionID goes here.
SELECT e.package_name,
        e.start_time,
        e.end_time,
        e.status,
        emc.package_path,
        CAST(emc.property_value AS VARCHAR(1000)) AS connection_string
   FROM catalog.executions e
   JOIN catalog.event_messages em
     ON e.execution_id = em.operation_id
   JOIN catalog.event_message_context AS emc WITH (FORCESEEK)
     ON em.event_message_id = emc.event_message_id
    AND emc.property_name = 'ConnectionString'
    AND emc.context_type = 80 -- Connection Managers
  WHERE e.execution_id = @execution_id;

What I don't see is your ExecutionInstanceGUID in this table. What I see, though, is this ancient Connect item where there's the following story:

SSIS RunningPackage.InstanceID != System::ExecutionInstanceGUID though they should be equal.

So my conclusion is that ExecutionInstanceGUID is not related to execution_id, but some InstanceId column, in case you might have one in the SSISDB.

SQL Server 2012 Agent Job not providing full failure message for SSIS Packages

SSIS handles errors in two ways.

One is raising an error. The other is failing a task/container/package.

The two are actually quite separate, and you can easily see one without the other.

To bubble an error up to the client (in this case, SQL Agent and its log) you would have to avoid catching the error somewhere. I suspect you might have an error handler there in some, or you have some of those convenient red arrows in there...

Of course, I see many times when people have errors that aren't causing anything to actually fail, so the package just keeps running. But that's another story...