Go Premium for a chance to win a PS4. Enter to Win

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,182 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
  • 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

971 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