Solved

Can't Schedule DTS package - Access Denied

Posted on 2004-09-07
14
331 Views
Last Modified: 2008-02-01
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 .....
0
Comment
Question by:laukratkin
  • 6
  • 3
  • 2
14 Comments
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11997299
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....

Jay
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 11997360
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.

AustinSeven
0
 

Author Comment

by:laukratkin
ID: 11997653
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..
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 11998253

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.

AustinSeven



   
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 11998326
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.

AustinSeven
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 10

Expert Comment

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

Jay
0
 

Author Comment

by:laukratkin
ID: 12000986
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 http://support.microsoft.com/?kbid=322746
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??
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12004256
sorry... I just remembered that DTSTRUN needs to be called as shell...

exec xp_cmdshell 'DTSRun /~Z...'

Glad you're getting closer.

AustinSeven
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12004296
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.

AustinSeven
0
 

Author Comment

by:laukratkin
ID: 12005729
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...



0
 
LVL 10

Accepted Solution

by:
AustinSeven earned 500 total points
ID: 12014111
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.

AustinSeven
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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 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

12 Experts available now in Live!

Get 1:1 Help Now