August 1, 2014 at 10:18 am
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
EXEC dbo.sp_enum_login_for_proxy
proxy_idproxy_nameflagsnamesidprincipal_id
23Run_SSIS0Server1\LocalUserxxxx30
Message
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... :crazy:
Elizabeth
August 1, 2014 at 12:25 pm
Quick thought, what are the security properties of the package? Have you tried initiating the download from the application server using the package runtime credentials?
😎
August 1, 2014 at 12:28 pm
I'm sorry, I don't know what you mean. Can you give me more information?
August 1, 2014 at 12:36 pm
Elizabeth.Block (8/1/2014)
I'm sorry, I don't know what you mean. Can you give me more information?
Unfortunately I do not have BIDS 2K8 at hand so I'm going by my gay cell memory here (which has a high number of bad sectors). If you select the package in the solution explorer and press F4, you get the properties of the package. Down at the bottom there should be some stuff about security, what does that say?
The other thing is, have you tried to RDP (Remote in to) the actual server and using the simplest of means (command line or any file transfer software) connect to the file server from where you are pulling the files from. Of course you will have to test this as the windows user in which scope the package runs.
😎
August 1, 2014 at 12:47 pm
Eirikur Eiriksson (8/1/2014)
Elizabeth.Block (8/1/2014)
I'm sorry, I don't know what you mean. Can you give me more information?Unfortunately I do not have BIDS 2K8 at hand so I'm going by my gay cell memory here (which has a high number of bad sectors). If you select the package in the solution explorer and press F4, you get the properties of the package. Down at the bottom there should be some stuff about security, what does that say?
The other thing is, have you tried to RDP (Remote in to) the actual server and using the simplest of means (command line or any file transfer software) connect to the file server from where you are pulling the files from. Of course you will have to test this as the windows user in which scope the package runs.
😎
Thanks, Erikur, I totally understand about not remembering esoteric details! :>) I'm not finding the security properties of the package. I'll hunt around some more.
I'll see if I can run the package from sqlcmd using the windows account that should run this. It's been awhile since I've use sqlcmd.
August 1, 2014 at 6:00 pm
Well this was a bear but I've got it now. The customer support from Pragmatic Works is great. They helped me get the package encryption set correctly. Here's the synopsis.
•I set the Package ProtectionLevel to EncryptSensitiveWithPassword. This is needed so the impersonation doesn’t get dropped in the SSIS package. After setting the Package ProtectionLevel, you enter the password in PackagePassword, which only needs to happen once.
•To make this able to connect to the SFTP site I had to have the SQL job run as the account with access to the SFTP site. I did this by creating a SQL credential and a SQL proxy account.
•I added a proxy to the SFTP connection in Task Factory because the account used to connect to the SFTP was a local account, which can’t get through the firewall between the source server and the SFTP site. We will have to create a domain account for this purpose. Currently it’s using another domain account.
•In the Task Factory tool by Pragmatic Works there is a debug feature. To use the debug feature you right click on a TF component and rename the component by appending “—DEBUG” to the original TF component name. The click on the properties of that component and select “Logging Mode” and choose Enabled. Then click on Log file location and set the name and location of the text file with any error messages.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply