I'm tearing my hair on this security issue. I've got an SSIS package that runs fine in BIDS(2008) but when I deployed the package to the server and assign a proxy to run the package I get the error below. I'm using Pragmatic Works Task Factory to download files from an SFTP site, which works fine in BIDS. The job step running the package is a local windows user on the server which has an account on the SFTP site. I've verified that the LocalUser account can upload and download files from the SFTP site. I've given the LocalUser permissions on the folders on the file system that it downloads files to. I even gave the LocalUser temporary sysadmin permissions on Server1 and still got the authentication failed error message.
Proxy Account: Run_SSIS, which has permissions on the SSIS Package Execution subsystem
Credential: DW_SFTP is mapped to the Server1\LocalUser account
Server1\LocalUser account has datareader/writer on user database, db_ssisadmin and SQLAgentOperatorRole on msdb
Results when runing
--View logins provided access to proxies
proxy_id proxy_name flags name sid principal_id
23 Run_SSIS 0 Server1\LocalUser xxxx 30
Executed as user: Server1\LocalUser. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:58:18 AM Error: 2014-08-01 08:58:19.75 Code: 0x00000000 Source: Download SFTP Files ExecuteTask Failed: Description: Authentication failed End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:58:18 AM Finished: 8:58:19 AM Elapsed: 1.297 seconds. The package execution failed. The step failed.
SQL Version: 2008 R2
Windows Version: Server 2012
Any help would be greatly appreciated! I'm on a tight timeline and am stuck...