Tracing Anonymous Logons
In a recent project, I had to execute an SSIS package deployed to the catalog from a stored procedure. The package uses Windows authentication to connect to the data sources. When executing the package from the catalog or from the stored procedure, the package immediately fails and the following two errors are logged:
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Source” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E4D Description: “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.”.
The culprit of course is that this scenario requires Kerberos delegation because of the two-hop authentication required:
- From SSMS on my development machine to the server where the SSIS package is executed
- From the SSIS server to the data source.
By default, the SSIS server doesn’t have the calling user’s password and the request to the data source goes under the anonymous logon. The easiest way to confirm this is to remote in to the SSIS server and execute the package there by right-clicking the package in the catalog and clicking Execute. If it works then configuring Kerberos delegation is in order. The process is simple but requires some assistance from your helpful system services department and specifically from your AD administrator because it requires a small change to the calling Windows account (yours and other accounts that will be used to run the stored procedure).
Other possible workarounds:
- Change all data sources to use standard security (SQL login and password)
- Executing the package under the SQL Agent where you can specify a Windows proxy account. This works because there is a single hop involved in this case.
