March 8, 2018 at 7:32 am
Hi,
I'm debugging an issue with executing a ssis package. This package calls out to a HTTPS service to download data and load it into a SQL table. Connections made using HTTP connection from a script task with basic security.
SSIS = server A
SQL = server B
HTTPS = server C
Management = server D
Server C is on a third-party domain. Server A, B, D are all on the same domain.
For debugging purposes I'm running the package in the SSISDB from SSMS.
The package runs okay from server A.
When the package is executed remotely from Server D the package fails with
System.Runtime.InteropServices.COMException (0xC001600C): Server authentication failed. This error occurs when login credentials are not provided, or the credentials are incorrect.
I don't believe this is related to Kerberos as we're not using Windows Security.
Looking at a network trace I can see the HTTP request going out to the HTTP service and connections coming back for both executions from server A/B. Although, for server A there are many more TCP frames coming back as data is returned.
SSIS is being run with a local user account.
We have a firewall to get through but no proxy. Port 80/443 is blocked except for exceptions - the URL in the HTTP call is an exception.
Any ideas of how to troubleshoot this?
March 8, 2018 at 9:21 am
does the sql server agent account on server D have rights to A and B?
March 9, 2018 at 1:48 am
yes
March 9, 2018 at 8:24 am
Please provide more detail about how Server D is executing an SSIS package on Server A. Unless I am missing something I didn't think this was possible unless you execute and SQL Agent Job on Server A remotely from Server D.
March 12, 2018 at 11:04 am
We're executing it using SSMS - connecting to the sis server A and running the package from the SSISDB.
Note, I've since tried to run the package on the server A via SQL Agent and the package gives the same error.
Simplified code is :
var connMgr = ComponentMetaData.RuntimeConnectionCollection.GetRuntimeConnectionByName(Variables.pvDataConnection).ConnectionManager;
var hcc = (HttpClientConnection100)connMgr.AcquireConnection(null);
hcc.Timeout = 300;
hcc.UseServerCredentials = true;
hcc.ServerUserName = "username";
hcc.ServerPassword = "password";
_downloadBuffer = hcc.DownloadData();
I wonder if there is a better HTTPS object to use. We're connecting to Apache on a Redhat server.
March 13, 2018 at 4:36 am
Going to give it a try with.
.Net Web Client
Seems the SSIS objects are flaky.
March 13, 2018 at 4:51 am
It does seem to me that this is a Windows security issue. When run from SSMS all is okay so it would have the security context of the user who is logged in. When run through SQL Agent Job it will be the security context of the SQL Agent Service Account. Not my forte, but I would be looking at Windows Event logs first. I would also be asking questions about proxy rules to our infrastructure guys in case the call can't get through.
March 13, 2018 at 6:58 am
Hi Tim,
Been through all this. I do agree with you that it's authentication related. We get the same error when using the same account that the package was built with though. Note, package is built with BIML but I doubt this is related.
I've ran out of time so I had to re-write using a different HTTP object from .Net. Problems go away with WebClient.
Seems the .Net objects are more powerful than SSIS ones. Good of Microsoft to let us reference .Net in the VSTA.
To be fair many of the examples using HTTPConnection seem to not use authentication. Now I know why 🙂
Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply