Solved

Can't Schedule DTS package - Access Denied

Posted on 2004-09-07
14
336 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

831 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