June 11, 2009 at 7:32 am
I'm running into an issue where developers can't fully test an SSIS package on their local boxes because of FTP and Email issues. Our policy doesn't allow that.
To get around this, I'm having them create their package on a share on the dev server. It will still fail when run locally and I understand why. We are using SQL Server to store the configuration as well.
It's not an option for them to RDC into the server and open up BIDS to run it, so the only way I can come up with is to have them use the agent through SSMS to execute or schedule a job.
Right now, both of those options aren't working and I need to see what I missed.
When they try to execute it from the agent they get:
"Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed. "
I put them in the SQLAgentUserRole Role thinking I could get by without doing a proxy.
I tried to go to the server myself and execute it as sa and get the same thing.
What am I missing?
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
June 11, 2009 at 7:40 am
What step is failing? Have you gotten a detailed debug log from SSIS?
June 11, 2009 at 7:50 am
I know you might have already done this: but a second shot..
Goto Component Services under Administrative Tools--> Click on component Services -->computers--> My computer-->Dcom Config-->msDtsServer-->Right click properties then Security, there are 3 containers "Launch and Activation permissions", "Access Permissions", "Configuration Permissions" click on Edit and apply the group users needing access to Lauhch the SSIS on local/Remote machiens.. Better add them to all 3 containeers.. Developers should be able to gain access for testing things...specially on Dev machines...
Restart SSIS Services and you should be good.. if this does not work.. restart SQL server Enginer Service.. and it should...
Maninder
www.dbanation.com
June 11, 2009 at 8:38 am
It's actually not failing on any step when run straight out of BIDS on that development server.
We are running it though the agent as file system. Does the MsDtsServer issue still matter?
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
June 11, 2009 at 8:46 am
Strange, it seems as though it has to be security/permissions then for the main package. Is there a proxy account you can set just for a test?
June 11, 2009 at 8:58 am
Did you check the permissions to the Folder where the SSIS package is located.
Maninder
www.dbanation.com
June 11, 2009 at 9:47 am
Steve Jones - Editor (6/11/2009)
Strange, it seems as though it has to be security/permissions then for the main package. Is there a proxy account you can set just for a test?
I'm in the process of that right now.
I agree about the permissions thing, and I believe this error points to it:
"Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed. "
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply