xp_cmdshell [modified]

[It_tech]

Hi All, I have a very strange problem with xp_cmdshell. Here is a Stored Procedure that is meant to execute a VB script file.When i run the VB Script independently it works fine. But when i execute the stored procedure that is pointing to this VB script file it runs with No error But Doesnt do the work. Stored Procedure USE [Database] GO /****** Object: StoredProcedure [dbo].[sp_SendSMS] Script Date: 06/27/2010 10:14:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_SendSMS] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @Numbers NVARCHAR(200) DECLARE @Body NVARCHAR(160) DECLARE @Command NVARCHAR(500) -- Insert statements for procedure here SET @Numbers = '00442345672' SET @Body = CAST(GETDATE() AS NVARCHAR(20)) + ' Test Message' -- TODO: Get Revenue Statistics and append them to @Command SET @Command = 'd:\test\SendSMS.vbs /Numbers:' + @Numbers + ' /Body: "' + @Body + '"' EXEC xp_cmdshell @Command END The VB script file is under test folder on D drive which has Read write permission set on the folder. Set WshShell = WScript.CreateObject("WScript.Shell") sNumbers= WScript.Arguments.Named.Item("numbers") sBody= WScript.Arguments.Named.Item("body") URL = "http://sampleweb.com/samplepage.aspx?IFVERSION=210000&MESSAGETYPE=10&OADC=00447956053317&MESSAGEID=3333&RECEIVETIME=20070320151916&BODY=This is my 4th message on the LIVE TV&MCLASS=2&HEADER=0048001&DESTADDRESS=1234&CONNECTION=MIG01l1&DCS=240&RETRYCOUNT=3&PID=0&AVSTATUS=1&GUID=9BBA5E93-390C-450C-94C3-1122E5D4858B" WshShell.Run(URL) Set WshShell = Nothing When i run the VBS file Independently it works fine.However when running the stored procedure above which contains the file i dont see the file being excuted. Could You please advice what is going Worng? Thanks for your time.

modified on Sunday, June 27, 2010 6:43 AM

[J4amieC]

So let me get this straight; you're using a stored proc, to execute a VBS file which makes an http request to send an SMS? Multiple points of failure doesnt even begin to describe this monster. Anyway, in answer to your question, its almost certainly a permissions problem. When you execute the VBS file directly, its running in the context of the currently logged in user. When you run it using xp_cmdshell its running in the context of whichever user SQL Server is running as.

[It_tech]

Thanks for your reply.Its Much appreciated.I have already checked and changed the user who loggs in as An is An administartor. Is there any thing i have to do to force the Login name to remain as the one who had access to that particualr folder. Many thanks for your Info.

[It_tech]

Thanks for the Reply. I have checked the login on Sql Server 2008 using Services.msc and changed the login to the login i use for windows. Its is still the same. Is there any advice you can suggest? Thanks.

modified on Monday, June 28, 2010 9:34 AM

[J4amieC]

There are so many things wrong with that response.

It_tech wrote:

I have already checked and changed the user who loggs in as An is An administartor

That is why you can run the VBS manually. your logged in user is an admin, and as an admin has rights to run the script. SQL Server runs as a service, it knows nothing about your logged in user, and has a completely different set of permissions.

It_tech wrote:

Is there any thing i have to do to force the Login name to remain as the one who had access to that particualr folder.

This problem has exactly nothing to do with folder permissions. You can give a user permission to read a file from a folder, but they may still be denied things like "Execute processes". If you have acccess to sys admins, ask them for help. If you dont then start reading up on the permission system in operation for the OS you're using. It varies quite substantially between older generation servers like Win2K and new generation servers like Win2K8. Finally, nobody here will be able to help you on this problem, as none of us have access to your servers, and all servers are set up differently.

[It_tech]

Thanks for your Help.