Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SS2008Re: SQL JOB with xp_cmdshell Security related issue Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 4:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:44 AM
Points: 29, Visits: 176
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
Post #1469975
Posted Wednesday, July 3, 2013 5:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:04 PM
Points: 7,141, Visits: 12,768
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
Post #1469994
Posted Wednesday, July 3, 2013 6:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:44 AM
Points: 29, Visits: 176
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
Post #1470013
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse