SSIS Task Fails When Not Logged In with the Associated User

0

I have a very simple SSIS package that downloads a file from a https site using a VB Script based on WebClient. I have it set up as a SQL Agent job running a SSIS Package step set to run as my "SSIS Proxy" proxy account. "SSIS Proxy" in turn is set to use a specific domain user (let's call this "SSISUser"). This domain user has interactive logon rights on the server this is running on. The task runs perfectly under the following situations:

  1. "SSISUser" has a interactive logon session open on the server (even if it is disconnected).
  2. A cmd prompt window is open that as been run as "SSISUser" (strangely the task still runs OK after I close this cmd prompt and until I logoff the user session i opened it under...)

If neither of these is true the task fails with the following message in the job history log: Message

Executed as user: domain\SSISUser. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 3:45:01 PM Error: 2018-02-05 15:45:32.16 Code: 0x00000000 Source: Download the Report Download File Description: Download failed: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. End Error Error: 2018-02-05 15:45:32.16 Code: 0x00000006 Source: Download the Report Description: The script returned a failure result. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:45:01 PM Finished: 3:45:32 PM Elapsed: 30.982 seconds. The package execution failed. The step failed.

I have tried a few things with the help of other questions in stackoverflow and other places but no luck:

  • Originally was using the HTTP Connection Manager in SSIS but found someone had the same issue and solved it by changing to WebClient. No luck for me.
  • Executed the job as a CmdExec instead (dtexec) but same issue.
  • Chased around with certificate issues but the certificate for the site seems to be a proper trusted authority one (not self signed).
  • Played around with adding the WebProxy into the script as explicitly coded but made no difference.
  • Set the server webproxy settings via netsh but no difference.

I would be happy with a workaround if anyone can suggest something to try as long as it is not bypassing security (ie global ignore certificates, etc). It just seems there needs to be some sort of environment active for "SSISUser" for the https access to function?!?

sql-server
https
ssis
etl
environment
asked on Stack Overflow Feb 6, 2018 by RowdyDoc • edited Feb 6, 2018 by Hadi

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0