Solved

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

Posted on 2010-08-25
9
1,836 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

757 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now