Access denied when package is run via SQL Server agent job

Question

Post reply

Access denied when package is run via SQL Server agent job

DerbyNeal

Hall of Fame

Points: 3854
More actions
July 8, 2011 at 9:35 am

#241144

Hi,
I have a SSIS package that uses an Execute Process Task. The task accesses a batch file on another server that processes a csv file. If I run the
package manually using a Service Account (xxxx), the package runs successfully. However, if I run the package within a
SQL Server Agent Job, the job fails giving the following error message:
Executed as user: xxxx. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64 bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 2011-07-08 00:00:00:00 Code: 0xC002F304 Source: Execute TaskSched file to produce csv file Execute Process Task.
Description: An error occurred with the following error message: "Access is denied". End Error DTExec: The package execution returned DTSER_FAILURE (1).
Started: 00:00:01 Finished 00:00:04 Elapsed 4.14 seconds. The package execution failed. The step failed.
The same Service Account is used in both manual and SQL Job examples. I've checked the associated Credential and Proxy accounts and they appear to be fine.
The credential is using the service acount as the identity.
The Proxy has the following settings:
General tab
ActiveX Script
Operating system (CmdExec)
SQL Server Ingetration Services Package
Principals tab
xxxx - SQL Login
db_datareader - Msdb role
db_datawriter - Msdb role
db_dtsoperator - Msdb role
db_dtsuser - Msdb role
db_dtsadmin - Msdb role
public - Server role
sysadmin - Server role
Reference tab has the associated job listed
Any ideas please?
Thanks in advance,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

Under "Executed as user: xxxx", is this user the same as the service account?

Do you need to use credentials? The job could be owned by sa.

Perhaps I didn't fully follow everything..

CEWII

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 2

Yes, 'xxxx' IS the service account. This account has the required privileges on both servers. The 2nd server is VERY locked down, which is why I'm using credentials etc

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

I was a little confused where you said if you run it with the service account it succeeds but as a job it fails. Can you describe how you did this?

If the service account has the rights then I don't see the need for a credential, using sa as the owner will cause the job to run as the service account.

CEWII

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 4

AAAARGH... This is so frustrating.

I think I have supplied sufficient details in my related posts.

I can't use the 'sa' account. The sa account doesn't have the rights to the 2nd server hence the following message when I use the sa:

'Access to the path \\servername\pathway\master.xml is denied'.

I had mentioned that the 2nd server is VERY locked down, which is why we needed to create a unique service account.

If I don't use a Proxy/Credentials, then the following message appears:

'Non-SysAdmins have been denied permissions to run DTS Execution job steps without a proxy account. The step failed'

Can anyone else help please?

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

DerbyNeal (7/11/2011)
AAAARGH... This is so frustrating.
I think I have supplied sufficient details in my related posts.
I can't use the 'sa' account. The sa account doesn't have the rights to the 2nd server hence the following message when I use the sa:
'Access to the path \\servername\pathway\master.xml is denied'.
I had mentioned that the 2nd server is VERY locked down, which is why we needed to create a unique service account.
If I don't use a Proxy/Credentials, then the following message appears:
'Non-SysAdmins have been denied permissions to run DTS Execution job steps without a proxy account. The step failed'
Can anyone else help please?

You forget apparently that this is a forum with unpaid volunteers, who are taking some of their spare time to help you with your problem.

You can boss your own payed minions around, but not here. Elliot is one of the more knowledgeable people around here, certainly when it comes to SSIS. So no, probably you didn't supply sufficient details. Such as how you ran the package manually with the service account.

Did you check if the permissions are set-up OK for whatever you are trying to execute with the Execute Process Task?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 6

Hi Koen,

Thanks for your reply... I don't believe I bossed anyone around. Also, I DO appreciate and haven't forgotten that this is a forum with unpaid members. I was just expressing frustration.

Thanks & Regards,

Neal

PS - I don't know what minions you are referring to

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 7

You are missing an important fact about sa, I understand that sa has no rights on the other server, HOWEVER, when a job is owned by sa it is run as the service account of SQL Agent. The OS doesn't know about sa either on the local machine. You have stated that when you run the code AS the service account it works. Set the job owner to sa and try it, worst case it doesn't work. But based on what you have said it should.

Also, Koen, thank you for the compliment.

CEWII