Sql-server – Executing SSIS catalogue package from a different machine, authentication fail

sql serverssis

I am having an issue running a SSIS package which uses a C# script component to carry out a HTTPS file download and load the data into a SQL Server database.

On our infrastructure we have a management (MGT), SQL Server (SQL), and Integration Services (SIS) machines. The package is deployed into the Integration Services Catalog on the SIS machine. The servers are on the same domain but on different vlans.

When the package is executed from SQL Server Management Studio (SSMS), running on the SIS machine, executing from Integration Services Catalog the package runs without issue.

However when I open SSMS on the MGT machine connect to the SQL Database on the SIS machine and run the package from the same catalog I get an error message.

An error occurred while executing package xxx in the container DFT – Load 1260 the error details are

0, System.Runtime.InteropServices.COMException (0xC001600C): Server authentication failed. This error occurs when login credentials are not provided, or the credentials are incorrect.

at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSHttpClientConnection100.DownloadData()
at ScriptMain.PreExecute()

The package execution stopped execution. Please investigate.

The code being ran within the script task within the package is

var connMgr = ComponentMetaData.RuntimeConnectionCollection.GetRuntimeConnectionByName(Variables.pvDataConnectionName1260).ConnectionManager;
var hcc = (HttpClientConnection100)connMgr.AcquireConnection(null);
hcc.Timeout = 300;

if (Variables.pvDataConnectionAuthorisationMode1260 == "Server")
{
    hcc.UseServerCredentials = true;
    hcc.ServerUserName = Variables.pvDataConnectionUsername1260;
    hcc.ServerPassword = Variables.pvDataConnectionPassword1260;
}
_downloadBuffer = hcc.DownloadData(); //this is the line that fails

connMgr.ReleaseConnection(hcc);

The URL being requested is along the lines of https://reports.abc.com/def/data_extracts/filename_20170815.csv

The URL has requires a username and password which we are providing to the script task as per the code above.

SPN's for Kerboros have been configured. And I can verify the connection is using Kerboros auth. I've enabled kerboros logging but nothing is logged in System event log when the package executes.

Any ideas what else might be causing the authentication to fail? or if there is any other logging I can do?

Best Answer

You're on the right track, but I suspect you're still running into the double-hop issue. SPNs are a step in the right direction to resolve this, but next you need to setup delegation for your SQL Server Instance so that it can properly pass along the AD token.

I will recommend that you setup constrained delegation for SQL Server, but any delegation adjustments will require an account with Domain Admin privileges on your network, so you may need to loop in another person or team depending on your company's structure.

The link above is for the older versions of Active directory, but the same approach will likely apply if you're running a newer version.

Steps 3 - 6 on this article about configuring delegation for SSRS are also a good reference point and SSIS is no different from SSRS where these steps are concerned.

Related Question