Can't Schedule DTS package - Access Denied

Win 2000 SP4 SQL 2000 SP3 - I have created a cube etc and am really happy - only to get totally frustrated by the fact I can't schedule the damn thing.  I have created a local package which executes fine but when it's scheduled I get this error:

executed as user: LOWEA\Administrator. The process could not be created for step 1 of job 0xD2AE7FFDBB6F5844AAF4051ED4B461E5 (reason: Access is denied).  The step failed.

Have looked high and low for a solution and followed an Article from MS about usual DTS security issues
but the LOWE\Administrator profile as quoted above is the account that starts all the SQL services and the account the server is currently logged on with.

I have spent ages b uilding complex dimensions and cubes to fufill a project and can't believe that I am stuck on the final hurdle - if the thing did not work at all great but the fact I can execute it just not autmoate the execution is driving me nuts .....
Who is Participating?
AustinSevenConnect With a Mentor Commented:
There should be no reason why it should not run in QA.  Make sure you run xp_cmdshell in the context of the Master db and make sure you enclose the entire DTSRun command line in single quotes.

exec master..xp_cmdshell 'DTSRun /~Z...'

However, what you're saying about having to include the full path in order to execute DTSRun at the command line doesn't sound right.   I'm pretty sure the default behavior on Windows servers I've worked with is that the PATH is setup to allow DTSRun to execute (ie. without specifying the full path).

One way to test this is to include the full path in the QA xp_cmdshell command line...

exec master..xp_cmdshell '"c:\program files\...\DTSRun" /~Z...'  

nb. I think you will need to use double quotes to enclose the path as there are spaces in it.  ie. ' "c:\program files\...\DTSRUN" /~Z...'

Maybe you need to address that PATH issue here.  I'm not sure it'll the complete answer to the problem but you're getting close now.

Jay ToopsCommented:
If you are Running WINDOWS 2000 or better.

Go to the Service Manager in the Administrative Tools area and then you  locate and Right Click on the SQL Agent Service..goto properties ....

The SQL Agent Service is generally run by LocalSystem...
.Change the Owner to whichever user u want to in the options provided on the Properties dialog box.

if you're using mail sp_sendmail  fix your SQLMail settings to use the new account/profile
Also, it's usually best practice to start BOTH the agent account and the SQL Server account with a domain admin
(if you have a domain) account....

Are there other non-DTS jobs on this SQL Server and do they run ok?    If there aren't other jobs, create a simple test job, schedule it for a few mintues in advance and see if it runs.   If it runs, there isn't a problem with the SQL Agent service account.

Does the DTS job (that has been created when you scheduled the package) run interactively?   ie. if you right-click on it in EM and select 'Start'.  

Another related check is to copy the DTSRUN command line from the scheduled job (that has been created when you scheduled the package) into a QA session and try to run it from there.   Does it run?

If you haven't already tried the above, hopefully the results will point you in the right direction.

I'm not at all sure if this is anything to do with it but also check the Login under which the package has been saved.  In the past I've had a few permssions issues that have been related to this.

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

laukratkinAuthor Commented:
Hi - There are no other jobs - I have tried to create a 'simple test job but to be honest am not to sure where to start (e.g new job new step - I have not touvhed on these as such so which is the easiest to quickly set up as a test)

The DTS job runs fine interactive no problems at all (which is the frustrating thing)
Copied the command into QA and it did not run - maybe I got the syntax wrong I just pasted it in and ran it.

The logon for the package is my Admin account within my domain -  same as all the SQL services log on accounts.  Pointers with creating the test job would be great..

In Enterprise Manager, find your server and expand it.  
Then exapand Management and then, under that, SQL Server Agent.  
Right-click on 'Jobs' and then 'New Job'.
Enter anything ('eg. test') for the job name in 'Name'
Change the 'Owner' in the pull-down selection field to 'SA'
Click the Steps Tab and then click the 'New' button.
Enter the step name - eg. 'test'
in the Command box, type some SQL like 'Select * from sysusers' and click 'OK'
Back in the Job Properties box, select the 'Schedules' tab and click the 'New Schedule' button
select the 'one time' button and enter a time a few mintues ahead from the current time, click ok and ok again to complete.

If the scheduled test job runs, your problem is related to DTS.   If it doesn't, it's a SQL Agent service account permissions problem.

Another test with the above job is to run it interactively to see if it works before the scheduled time it is supposed to run.    Again, if doesn't run interactively, there's a problem with the job (unlikely).  If it does run interactively an doesn't run scheduled, this backs up the conclusion about service a/c permissions.

I suggest you run the test again with the DTSRUN command line.  Make sure you copy and paste it exactly as is into a QA session.   It should work.    If it doesn't work, the problem is the DTRRUN command line.  You would then be advised to delete the DTS created job and re-create it via the DTS package.

Hope this gets you closer.  Please try to be methodical in your testing.


and...  If you narrow it down to a simple fact that a standard job does not run scheduled but it does run interactively, change the SQL Server service accounts.  Unless you're running a cluster, local system would be good enough I think.

Jay ToopsCommented:
under enterprise manager click on the SQL Server Agent and check the properties to see if its running.

laukratkinAuthor Commented:
Ok I have done some 'fishing'  thanks for the tips and I think the DTS is in error the new job created worked fine scheduled etc - Initially when I set the DTS up I received an error :

DTSRun.exe - entry point not found etc etc..could not be located in the dynamic link library SQLRESLD.dll
After a while I thought I had found the fix
which was to alter the command to :

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn"DTSRun /~Z0xE1F9CEF3DFF0378BCDE737E738E15EE5D00B4FD1E6ECFF64D814F520A9348A977A4AA190504FDFE7AEAA4578254434C5E3541D07AA8B8BD06A67B3AF85AE9E01021619D6F8CD43FD69BE2B002955A8A617DDB0EC520DBA4A764D81058904B3498EBD7C2DF43B53940CD186C44F8CF9F10A940AA2EE6B186458AA153AEB89C02C1903414532522A060AA4EF  

(e.g insert the file path at the start.)  This got rid of the DTS error and I was left with the authority problems If I copy the cmd above into the QA it does not work whereas any other cmd does so I guess it is in error any ideas what the syntax should look like??
sorry... I just remembered that DTSTRUN needs to be called as shell...

exec xp_cmdshell 'DTSRun /~Z...'

Glad you're getting closer.

or... Just go to the windows/dos command prompt and paste in the DTSRUN command line.   I tend to use xp_cmdshell from QA though.

laukratkinAuthor Commented:
Thanks for the help totally baffled !!!

Can get this cmd to run in DOS
DTSRun /~Z0xE1F9CEF3DFF0378BCDE737E738E15EE5D00B4FD1E6ECFF64D814F520A9348A977A4AA190504FDFE7AEAA4578254434C5E3541D07AA8B8BD06A67B3AF85AE9E01021619D6F8CD43FD69BE2B002955A8A617DDB0EC520DBA4A764D81058904B3498EBD7C2DF43B53940CD186C44F8CF9F10A940AA2EE6B186458AA153AEB89C02C1903414532522A060AA4EF
as long as I am in the correct directory c:\prog files etc.  can't get the thing to run in QA guess that is why the SQL agent won't process
this cmd - presumably there is a different syntax somewhere between DOS and SQL.

Anyway wrote a batch program to process the DTS called daily from within the SQL agent scheduler - bit of a workaround but will do me fine and
has stopped me tearing my hair out...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.