July 9, 2010 at 9:29 am
I have a stored procedure called uspCallBatchFile, that calls xp_cmdshell, which tries to execute a batch file located on the server. This batch file, in turn, calls a VB executable. This VB executable connects to WebSphere MQ and transfers messages from MQ to SQL Server. The VB application is programmed to send me an email if the queue manager is empty. This is how I know the VB application is launched. There is no GUI.
The user clicks a button from an Access client application which launches the stores procedure. This client application uses SQL Server authentication to log into the SQL Server database.
I checked the server proxy account option, which allows xp_cmdshell to impersonate the login (who is not a sysadmin).
Problem:
When I click the button on the client application, the stored procedure gets called but it doesn't launch the VB application. I get no email. As an additional proof, I added some code in my batch file that sends status messages to a log file located in the same folder, and there is nothing in the log file when the user clicks the button from the client app.
Everything works fine when I run the batch file directly. That's because I'm an admin on the server. But doesn't work from the stored procedure.
When I check the Event Viewer on the server, I see the proxy account (called sqlproxy) when I click Event Viewer --> Security node.
Here is my stored procedure:
ALTER PROCEDURE [dbo].[uspCallBatchFile]
(
@pcBatchFileVARCHAR(100)
)
AS
BEGIN
DECLARE @vcPathBatch VARCHAR(255)
DECLARE @vintReturnCode INT
SELECT @vcPathBatch = cValue
FROM dbo.vwSysConfig
WHERE cName = @pcBatchFile AND intConfigGroup = 0
IF @vcCPathBatch IS NULL OR @vcPathBatch = ''
RETURN 1
ELSE
BEGIN
-- Execute batch
BEGIN TRY
EXEC @vintReturnCode = master.dbo.xp_cmdshell @vcPathBatch
RETURN @vintReturnCode
END TRY
BEGIN CATCH
DECLARE @msg VARCHAR(100)
SELECT @msg = ERROR_Message()
RAISERROR(@msg, 11, 1) WITH LOG
END CATCH
END
END
Basically the parameter used by xp_cmdshell is "C:\Apps\Metro\PromoMetroCom\PromoMetroCom.BAT" which is fetched from a table.
I tried many things:
1) I added the login in the master database and gave it the role db_cmdshell.
2) I granted the login permission to execute xp_cmdshell.
What could I be forgetting? I get no error message posted in the Event Viewer when the stored procedure is called. But, like I said, I see the sqlproxy in the Security node.
Thanks in advance.
Ray
July 9, 2010 at 10:53 am
Does the proxy account have permissions on MSDB to execute the dbmail procs? Does it have write permissions on the share where the log file is kept? Does it have permissions to whatever file / module that the Proc is supposed to run?
July 9, 2010 at 2:46 pm
I don't think the VB application uses the dbmail procs to send emails. But I'm wondering about the permissions you mentioned. I created the batch (.BAT) file on the server but I didn't assign any permissions.
How do I assign execute permission (on the exe file) to the proxy account?
July 12, 2010 at 5:43 am
Well that depends on where the .exe file is located. If it's on a UNC or Share drive, you just need to add the proxy account to that path's Security permissions and grant it the ability to Read and Execute the contents of that folder.
However, this requires the proxy account be a domain user, not a SQL Server only account (which might be your main problem, if the proxy is just a SQL only account).
Do you know anything about network & server permissions (non-SQL security)? If not, I suggest you get together with one of your network / server people and ask. Knowing this information is vital to getting SQL Server to work properly.
July 12, 2010 at 8:44 pm
The proxy account is a domain user. I asked my network guy to set up the security and it now works perfectly.
Thanks !!
July 13, 2010 at 4:08 am
Glad I could help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply