LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » Integration Services (SSIS) and DTS » Access denied while executing a windows process task uing Proxy/credentials

Access denied while executing a windows process task uing Proxy/credentials

Options

Previous Topic · Next Topic

Ram

Posted: Wednesday, March 10, 2010 10:17:22 PM

Rank: Newbie

Joined: 3/10/2010
Posts: 4
Points: 12
Where do you live?: USA

Hello All,

I have a SSIS 2005 package that is configured to use a windows Account as a credential that has ftp access. Basically we have a execute process task that is calling the ftp executable and passing in a script that will do the upload/download etc. When the job runs, the task fails with Access denied error. But when the same account is logged on to the server and tries to execute the same ftp job manually, it works. I am not sure what I am missing here. Please throw some ideas if you have implemented any thing like this.

Regards
Ram

Scott Whigham

Posted: Friday, March 12, 2010 5:17:45 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX

Hi there - I don't have access to a server to play with ATM but I can say that, when the job runs, it is running under the security context of the SQL Server Agent. You might just double check that the task is running under the proxy and not trying to authenticate using the Agent's Windows account.

Ram

Posted: Saturday, March 13, 2010 10:37:52 PM

Rank: Newbie

Joined: 3/10/2010
Posts: 4
Points: 12
Where do you live?: USA

Thanks Scott.

I am facing some difficulty in debugging SSIS package's and performing Error handling both at package level and child package/containers/tasks etc. I want to be able to log all errors using a custom script task and call the script in individual OnError event. Is there any best practice for performing Error handling like we do in ASP.NET apps? (I want to capture the user associated with the job and log it in the exception log.) BTW, the SSIS Training is awesome. I ramped up my skills in a week following your training.

Regards
Ram

Scott Whigham

Posted: Monday, March 15, 2010 10:32:15 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX

Thanks for the kind words, Ram Smile

Have you watched Chapter 3 on logging in my SSIS 2008 course? There are probably a few videos there that will help you find what you need, I think (maybe the last 3 videos): http://www.learnitfirst.com/Course/158/Chapter/259/Package-Management.aspx

Ram

Posted: Tuesday, March 16, 2010 2:25:43 AM

Rank: Newbie

Joined: 3/10/2010
Posts: 4
Points: 12
Where do you live?: USA

Hi Scott,

Yes, I did. they are very informative. I am having a tough nut to crack with a SQLServer2005 SSIS package that has a dataflow that fetches data from SQLServer table and populates it into an Excel file. Every thing work file in BIDS and via the Execute package utility. But fails with the following error in Server with a Proxy and credential configured. the permissions to the folder where the excel is there for the proxy account look fine.

Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "xxx" failed with error code 0xC0202009.

Can you please throw me some suggestions on this?

Regards
Ram

Ram

Posted: Tuesday, March 16, 2010 10:09:42 PM

Rank: Newbie

Joined: 3/10/2010
Posts: 4
Points: 12
Where do you live?: USA

Just an update on the Excel connection manager Issue, I found that the SQL Server Agent is running under a different user's windows account. Changed it to Local System and used a Proxy account with credential for the SSIS Package. Everything is normal after that. But it took a whole lot of time to figure out. May be it's time for me to learn the SQLServer DBA course.:-.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.