cmdexec works, xp_cmdshell does not for command in Agent job

Question

cmdexec works, xp_cmdshell does not for command in Agent job

Sha_

Ten Centuries

Points: 1299
More actions
March 20, 2010 at 11:46 pm

#146102

Hi all,
I am trying to run a Windows schtasks job on a remote server from a SQL Agent job.
If I simply have a jobstep using cmdexec, the schtasks command works fine. The one line is:
schtasks /run /tn jobname /s remoteservername
Now I need to add some sql logic that will disallow this command from running on the last day of the month. Being t-sql, I change the agent job from type CmdExec to Transact-SQL script, and use the script below (the command line job now called from xp_cmdshell). The switch to xp_cmdshell gives me Access is denied, despite the acct the Agent is run under is an Admin on the remote server.
Is there another way I can do this that will work? I would like to omit the use of xp_cmdshell if possible due to security reasons. ActiveX, perhaps?
Thanks in advance,
Sharon
DECLARE @yesterday datetime
DECLARE @startdate datetime
DECLARE @result int
DECLARE @LDMDate datetime
set nocount on
set @startdate=getdate()
set @yesterday=(select dateadd(d,-1,@startdate))
set @result=0
SET @LDMDate=(select convert(char(10), @startdate, 101))
SET @LDMDate=convert(char(10),DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@LDMDate),0)),101)
--next two selects are for the log output
select convert(char(10), @yesterday, 101) as 'Yesterday'
select convert(char(10), @LDMDate, 101) as 'Last day of the previous month'
if (select convert(char(10), @LDMDate, 101))=(select convert(char(10), @yesterday, 101))
select 'Yesterday equals the last day of the previous month. Therefore, will not run this report.'
else
begin
select 'Yesterday does not equal the last day of the previous month. Therefore, will run this report.'
EXEC @result = master..xp_cmdshell"schtasks /run /tn jobname /s remoteservername"
end
done:
IF (@result <> 0)
RAISERROR ('Job failed. Please check this job''s error log to determine the point of failure. Failing sql job...', 16, 1)
set nocount off

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

To use xp_cmdshell in SQL Server 2005 you have to enable it using the Surface Area Configuration program as it is disabled by default.

Check Books Online for additional information.

Sha_ Ten Centuries Points: 1299 More actions · Answer 2

It is enabled...I believe the "access is denied" error comes from the subsystem - a command run from cmdexec will probably have different security context than xp_cmdshell. So, I am trying to avoid xp_cmdshell altogether and find something else that can run command line to see if I can get around it. I am trying VB right now, but I don't know vb so it is difficult...

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 3

I can't say what is causing your issue...but

I have used a Windows utility called psexec to invoke processes on remote servers from within a SQL agent job. You might try that approach. You will probably have to download and install psexec - I believe it's freely available from a company called SysInternals.

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 4

Also, just remembered one more thing - if you decide to use PSExec, you will need to execute it one time interactively to answer a one-time licensing dialog. After the first time, the dialog no longer appears, so you can run in an automated fashion. This caused me some grief the first time I used it until I realized what was going on.

Sha_ Ten Centuries Points: 1299 More actions · Answer 5

Thanks, Old Hand! I am aware of the sysinternals tools but didn't think to use any here. Great idea. I will definitely give this a shot. I hope it has the ability to return accurate error codes...

Thanks again,

Sharon

bkatapa SSC Journeyman Points: 81 More actions · Answer 6

hope someone comes to my rescue. i have been trying to run a job step within sql server agent 2005 that triggers off a process on a remote pc using psexec. the problem is the step executes forever!

the commands am using are as below;

psexec \\remote-pc -u usename -p password "remote path of process"

these are contained within a batch program which am trying to schedule. the batch works fine on its own, calamity befalls only when i schedule with sqlserver agent. i have tried creating credentials and proxies with the maximum level of permissions but the woes continue. please help,

Jadube.