SS2008Re: SQL JOB with xp_cmdshell Security related issue

• chauhanparixit

  SSC-Addicted

  Points: 429

  More actions

  July 3, 2013 at 4:35 am

  #279805

  Hi Friends,

  I have migrated SQL JOB from SQL2005 to SQL2008, as below

  THe step description as below

  IF EXISTS (SELECT * FROM someTable)

  BEGIN

  Exec master..xp_cmdshell "'c:\mypath\someExecutable.exe'"

  END

  this someExecutable.exe shoule create 4 image files and 1 word file. if I run this executable from Command Prompt it is working fine.

  But if i run the same someExecutable.exe' through a SQL JOB, it is just creating 4 image files but not creating WORD document.

  Dont understand why.. actually this job is migrated from SQL 2005 and its working fine there.

  it looks like i may require some permission related thing with the executable or OFFICE.

  Please provide the solution, i know it's confusing.

  Thanks in advance

  Parixit Chauhan

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  July 3, 2013 at 5:02 am

  #1629481

  chauhanparixit (7/3/2013)

  ---

  Hi Friends,

  I have migrated SQL JOB from SQL2005 to SQL2008, as below

  THe step description as below

  IF EXISTS (SELECT * FROM someTable)

  BEGIN

  Exec master..xp_cmdshell "'c:\mypath\someExecutable.exe'"

  END

  this someExecutable.exe shoule create 4 image files and 1 word file. if I run this executable from Command Prompt it is working fine.

  But if i run the same someExecutable.exe' through a SQL JOB, it is just creating 4 image files but not creating WORD document.

  Dont understand why.. actually this job is migrated from SQL 2005 and its working fine there.

  it looks like i may require some permission related thing with the executable or OFFICE.

  Please provide the solution, i know it's confusing.

  Thanks in advance

  Parixit Chauhan

  1. Get rid of xp_cmdshell. You don't need it here.

  Job step 1, T-SQL: write the SQL to raise error when no rows exist. In the 'on success' of the job step say 'go to next step' and in the 'on fail' say 'quit job with success'.

  Job Step 2, CmdExec: run the executable. On success say 'quit job with success', and on fail say 'quit job with failure.'

  As for the executable, do you receive an error when you run it through the job? I assume Word is installed correctly because you said when you run it from the command line it completes successfully. Is the SQL Server 32-bit or 64-bit and did that change from 2005 to 2008? Another thing to check is whether your executable can handle running under 64-bit in case that changed. Some of the Office objects only run under 32-bit.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• chauhanparixit

  SSC-Addicted

  Points: 429

  More actions

  July 3, 2013 at 6:26 am

  #1629497

  Just found the resolution of this, actually it was using Word 2003 and the com component does not have any permission for Local system so it was not creating the Word file.

  THanks for your time and support