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


Trouble using proxy to run SSIS pacakage


Trouble using proxy to run SSIS pacakage

Author
Message
Elizabeth.Block
Elizabeth.Block
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 702
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_id proxy_name flags name sid principal_id
23 Run_SSIS 0 Server1\LocalUser xxxx 30


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
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15298 Visits: 18612
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?
Cool
Elizabeth.Block
Elizabeth.Block
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 702
I'm sorry, I don't know what you mean. Can you give me more information?
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15298 Visits: 18612
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.
Cool
Elizabeth.Block
Elizabeth.Block
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 702
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.
Cool


Thanks, Erikur, I totally understand about not remembering esoteric details! :>Wink 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.
Elizabeth.Block
Elizabeth.Block
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 702
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.
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