SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remote execution of SSIS package SQL 2016


Remote execution of SSIS package SQL 2016

Author
Message
leehbi
leehbi
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1898 Visits: 747
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?
alen teplitsky
alen teplitsky
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26863 Visits: 4905
does the sql server agent account on server D have rights to A and B?
leehbi
leehbi
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1898 Visits: 747
yes
tim.ffitch 25252
tim.ffitch 25252
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 200
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.
leehbi
leehbi
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1898 Visits: 747
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.
leehbi
leehbi
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1898 Visits: 747
Going to give it a try with.
.Net Web Client
Seems the SSIS objects are flaky.
tim.ffitch 25252
tim.ffitch 25252
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 200
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.
leehbi
leehbi
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1898 Visits: 747
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search