Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trouble using proxy to run SSIS pacakage Expand / Collapse
Author
Message
Posted Friday, August 1, 2014 10:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:22 AM
Points: 93, Visits: 594
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...

Elizabeth
Post #1598757
Posted Friday, August 1, 2014 12:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 2,214, Visits: 5,977
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?
Post #1598792
Posted Friday, August 1, 2014 12:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:22 AM
Points: 93, Visits: 594
I'm sorry, I don't know what you mean. Can you give me more information?
Post #1598794
Posted Friday, August 1, 2014 12:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 2,214, Visits: 5,977
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.
Post #1598796
Posted Friday, August 1, 2014 12:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:22 AM
Points: 93, Visits: 594
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.
Post #1598799
Posted Friday, August 1, 2014 6:00 PM This worked for the OP Answer marked as solution
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:22 AM
Points: 93, Visits: 594
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.

Post #1598840
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse