• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2219
  • Last Modified:

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.

0
DataDizzy
Asked:
DataDizzy
  • 6
  • 3
1 Solution
 
Reza RadCommented:
did you set proxy account for both machine? ( machine which run ssis package , and remote sql server machine )
0
 
DataDizzyAuthor Commented:
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.  
0
 
Reza RadCommented:
did the user account which has access to select data on remote server is exactly that user which runs the ssis package?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DataDizzyAuthor Commented:
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.
0
 
DataDizzyAuthor Commented:
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.
0
 
DataDizzyAuthor Commented:

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.
0
 
Reza RadCommented:
>>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/

0
 
DataDizzyAuthor Commented:
I was finally able to get the package to run in the job by setting the package source as File System rather than saving the package to the database.    I still don't undertand why it wouldn't work the other way, but at least it runs.

I do apologize for abandoning this thread for so long.  It was not intentional.
0
 
DataDizzyAuthor Commented:
I don't know why, but it worked.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now