xp_cmdshell error using SQL Server Authentication

[jpastika]

I am getting the following error when I try running a stored procedure that executes the xp_cmdshell extended stored procedure:

"Msg 50001, Level 1, State 50001
xpsql.cpp: Error 997 from GetProxyAccount on line 472"

I only get this error when I try executing the "parent" stored procedure when using SQL Server Authentication. Executing using Windows authentication works fine. Both the Windows user and the SQL user have execute permissions on all stored procedures. Any ideas?

Some additional background info: I am trying to use a trigger to execute the xsp_cmdshell procedure to run a DTS package which adds a message to a MS Message Queue. As I said it works great using Windows authentication, but I need to use SQL Server authentication, because the VB app I have connecting to the database (and triggering the trigger) only uses SQL Server authentication.

Thanks for any help or suggestions.

-Jeremy

 

[jpastika]

Well, I answered my own question. After about 6 hours of fiddling around with different security rights, moving users around, and banging my head, it turns out all I had to do was add my SQL Server user (the one used by the VB app) to the System Administrators group. This is because that is the only group that can execute extended stored procedures such as xsp_cmdshell. So now my trigger is adding messages to the queue and life is good.

I hope this information can help someone else.

-Jeremy

 

[ks1392]

Very good post, Jeremy. I experienced the exact same issue as you. I looked on the MS Support site to no avail and thankfully I found you message here on Tek-Tips. Tek-Tips comes through yet again!