Link to home
Start Free TrialLog in
Avatar of DataDizzy
DataDizzyFlag for United States of America

asked on

Run SSIS package connecting to remote domain w/windows authentication works in BIDS, fails in SQL Job

Hi,
I'm tasked with downloading data from a server on a remote domain that only used windows authentication. This download has been done manually by someone for well over two years (thankfully not by me and I feel for the poor soul who has been doing it) and is high on my management's priority list.

I have been given a domain account on the remote domain and we have set up our domain with a limited trust, so I can run BIDS with \runas.exe /netonly /user:<domain\account> and sucessfully download the data.  

Now that it works, I want to schedule this download so I created a credential with the remote domain account and a proxy and set up the job to run the SSIS package with SA and set the runas option in the package to use the proxy.    It fails with the following error:

---
Unable to start execution of step 1 (reason: Error authenticating proxy <domain\account>, system error: Logon Failure: The machine you are logging onto is protected by an authentication firewall.  The specified account is not allowed to authenticate to the machine.).  The step failed.
---

Any ideas on what I may be doing wrong or if there is another, possibly better, way to approach this?    

Thanks,
D.

Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

did you set proxy account for both machine? ( machine which run ssis package , and remote sql server machine )
Avatar of DataDizzy

ASKER

I have no control and only very limited access on the remote SQL server.  I only have the domain account on that domain with read access to selected views that I may download from.  
did the user account which has access to select data on remote server is exactly that user which runs the ssis package?
SA account is the owner of the package.  but the run as on the step of the job is set to the proxy account, which is set to be the domain account on the remote domain.
UPDATE:     The AD trust we setup between our domain and remote domain was setup as Selective Authentication, so we need to explicity allow remote domain accounts access.  

Now I'm getting the following error message:

Message
Executed as user: <domain\account>. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:40:44 PM  Could not load package <package name> because of error 0xC0014062.  Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user '<domain\account>'.).  The SQL statement that was issued has failed.  Source:   Started:  2:40:44 PM  Finished: 2:40:45 PM  Elapsed:  0.937 seconds.  The package could not be loaded.  The step failed.


Any help would be appreciated!  

Thanks,
D.

I found this article which solved the problem:
http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx

After adding the SQL Agent roles it still failed with the same message.   I then changed the job so that it was loading the package from the file location, rather than from the SQL Server and it worked.  

Thanks!
D.
>>I then changed the job so that it was loading the package from the file location, rather than from the SQL Server and it worked.
<<
so you deployed your ssis package incorrectly.
this is how to deploy your package and run it as a job good tutorial:
http://technotes.towardsjob.com/sql-server/steps-to-create-and-deploy-ssis-package-as-a-sqlagent-job/

ASKER CERTIFIED SOLUTION
Avatar of DataDizzy
DataDizzy
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I don't know why, but it worked.