Cscript from a SQL Stored Procedure?

Hopefully, someone can help me out with this.
I'm trying to run a .vbs script using cscript from a stored procedure that
gets called from a powerbuilder application (our commerce software)
that uses SQL SERVER 2000 as it's database.
Basically we are trying to add code that calls the .vbs script so that when
an invoice prints it also calls the .vbs script to print a packing list as
well.
The .vbs file simply formats some data, and creates a file.
I can run everything fine from Query Analyzer, however when the
stored procedure gets called from the application. I get this error :
CScript Error: Loading your settings failed. (Access is denied.)
Here is how the .vbs script is being called :
DECLARE @.STR varchar(255)
SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
exec master..xp_cmdshell @.STR, NO_OUTPUT
2265440 is simply an invoice no passed in as a parameter.
I've been able to establish that xp_cmdshell does run. Using :
SET @.STR = 'dir /p > c:\OUTPUT.TXT'
I was able to get the directory listing redirected to the output.txt file
successfully.
However, when the stored procedure is being called from the application
'cscript'
doesn't seem to execute.
Also our SQL Server is running under an administrator login, versus sa.
Any help is appreciated.
CarlosYou may want to check the proxy account. When non-sysadmins
execute xp_cmdshell, it will run under the security context
of the SQL Server Agent proxy account. You'll want to check
how the proxy account is configured and what permissions it
has.
-Sue
On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
>Hopefully, someone can help me out with this.
>I'm trying to run a .vbs script using cscript from a stored procedure that
>gets called from a powerbuilder application (our commerce software)
>that uses SQL SERVER 2000 as it's database.
>Basically we are trying to add code that calls the .vbs script so that when
>an invoice prints it also calls the .vbs script to print a packing list as
>well.
>The .vbs file simply formats some data, and creates a file.
>I can run everything fine from Query Analyzer, however when the
>stored procedure gets called from the application. I get this error :
>CScript Error: Loading your settings failed. (Access is denied.)
>Here is how the .vbs script is being called :
>DECLARE @.STR varchar(255)
>SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
>exec master..xp_cmdshell @.STR, NO_OUTPUT
>2265440 is simply an invoice no passed in as a parameter.
>I've been able to establish that xp_cmdshell does run. Using :
>SET @.STR = 'dir /p > c:\OUTPUT.TXT'
>I was able to get the directory listing redirected to the output.txt file
>successfully.
>However, when the stored procedure is being called from the application
>'cscript'
>doesn't seem to execute.
>Also our SQL Server is running under an administrator login, versus sa.
>Any help is appreciated.
>Carlos
>|||Sue,
Thank-you for your help. I revisted our security & proxy accout setup.
Turns out the proxy acccount we setup didn't have log-in permissions
to our Sql Server. The application that was calling the stored procedure
and making the xp_cmdshell call was on another server. Once I changed
the proxy account to a valid login on the Sql Server everything worked
fine. Again thanks!
Carlos
"Sue Hoegemeier" wrote:
> You may want to check the proxy account. When non-sysadmins
> execute xp_cmdshell, it will run under the security context
> of the SQL Server Agent proxy account. You'll want to check
> how the proxy account is configured and what permissions it
> has.
> -Sue
> On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
> <Carlos@.discussions.microsoft.com> wrote:
> >Hopefully, someone can help me out with this.
> >
> >I'm trying to run a .vbs script using cscript from a stored procedure that
> >gets called from a powerbuilder application (our commerce software)
> >that uses SQL SERVER 2000 as it's database.
> >
> >Basically we are trying to add code that calls the .vbs script so that when
> >an invoice prints it also calls the .vbs script to print a packing list as
> >well.
> >
> >The .vbs file simply formats some data, and creates a file.
> >
> >I can run everything fine from Query Analyzer, however when the
> >stored procedure gets called from the application. I get this error :
> >
> >CScript Error: Loading your settings failed. (Access is denied.)
> >
> >Here is how the .vbs script is being called :
> >
> >DECLARE @.STR varchar(255)
> >SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
> >exec master..xp_cmdshell @.STR, NO_OUTPUT
> >
> >2265440 is simply an invoice no passed in as a parameter.
> >
> >I've been able to establish that xp_cmdshell does run. Using :
> >
> >SET @.STR = 'dir /p > c:\OUTPUT.TXT'
> >
> >I was able to get the directory listing redirected to the output.txt file
> >successfully.
> >However, when the stored procedure is being called from the application
> >'cscript'
> >doesn't seem to execute.
> >
> >Also our SQL Server is running under an administrator login, versus sa.
> >
> >Any help is appreciated.
> >
> >Carlos
> >
>|||You're very welcome Carlos - thanks for posting back the results!
-Sue
On Wed, 27 Oct 2004 12:47:02 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
>Sue,
>Thank-you for your help. I revisted our security & proxy accout setup.
>Turns out the proxy acccount we setup didn't have log-in permissions
>to our Sql Server. The application that was calling the stored procedure
>and making the xp_cmdshell call was on another server. Once I changed
>the proxy account to a valid login on the Sql Server everything worked
>fine. Again thanks!
>Carlos
>
>"Sue Hoegemeier" wrote:
>> You may want to check the proxy account. When non-sysadmins
>> execute xp_cmdshell, it will run under the security context
>> of the SQL Server Agent proxy account. You'll want to check
>> how the proxy account is configured and what permissions it
>> has.
>> -Sue
>> On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
>> <Carlos@.discussions.microsoft.com> wrote:
>> >Hopefully, someone can help me out with this.
>> >
>> >I'm trying to run a .vbs script using cscript from a stored procedure that
>> >gets called from a powerbuilder application (our commerce software)
>> >that uses SQL SERVER 2000 as it's database.
>> >
>> >Basically we are trying to add code that calls the .vbs script so that when
>> >an invoice prints it also calls the .vbs script to print a packing list as
>> >well.
>> >
>> >The .vbs file simply formats some data, and creates a file.
>> >
>> >I can run everything fine from Query Analyzer, however when the
>> >stored procedure gets called from the application. I get this error :
>> >
>> >CScript Error: Loading your settings failed. (Access is denied.)
>> >
>> >Here is how the .vbs script is being called :
>> >
>> >DECLARE @.STR varchar(255)
>> >SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
>> >exec master..xp_cmdshell @.STR, NO_OUTPUT
>> >
>> >2265440 is simply an invoice no passed in as a parameter.
>> >
>> >I've been able to establish that xp_cmdshell does run. Using :
>> >
>> >SET @.STR = 'dir /p > c:\OUTPUT.TXT'
>> >
>> >I was able to get the directory listing redirected to the output.txt file
>> >successfully.
>> >However, when the stored procedure is being called from the application
>> >'cscript'
>> >doesn't seem to execute.
>> >
>> >Also our SQL Server is running under an administrator login, versus sa.
>> >
>> >Any help is appreciated.
>> >
>> >Carlos
>> >
>>