Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-08-25
9
Medium Priority
?
2,129 Views
Last Modified: 2013-11-10
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
Comment
Question by:DataDizzy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33525223
did you set proxy account for both machine? ( machine which run ssis package , and remote sql server machine )
0
 

Author Comment

by:DataDizzy
ID: 33525259
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
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33525322
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.

 

Author Comment

by:DataDizzy
ID: 33525341
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
 

Author Comment

by:DataDizzy
ID: 33526608
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
 

Author Comment

by:DataDizzy
ID: 33533340

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
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33539881
>>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
 

Accepted Solution

by:
DataDizzy earned 0 total points
ID: 34387031
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
 

Author Closing Comment

by:DataDizzy
ID: 34415441
I don't know why, but it worked.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question