execute xp_cmdshell - cant get it to work - no matter what - any one?

Question

execute xp_cmdshell - cant get it to work - no matter what - any one?

Lynn Hendricks

SSC Enthusiast

Points: 142
More actions
May 5, 2015 at 3:37 pm

#317924

I've tried every possible solution found on the web to get xp_cmdshell working from within a stored proc, called from within an application. The current_user when the stored proc is running is an application role. The system_user is the person logged in, lets say me(while I'm testing), and I have sysadmin rights(which is really puzzling). I get the execute permission denied message. I've setup the proxy account - a windows account, set up a local user in the master, with execute permissions on xp_cmdshell, then tried impersonating that user. Get the principle cannot be impersonated. Yes, I tried with and without impersonating.
Added the same local user to that database, and to the securables on the application role. same error.
Tried every step I found on getting xp_cmdshell to work(when goggling) and cannot. What could I be missing? I cant get the application role a direct execute permissions on the extended stored procedure, anyone know of any work arounds, Or have had to use this type of process in the past.
Put in over 8 hrs trying every possibility, help!!!
thanks!!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Hi and welcome to the forums. Starting with the basics, do you have advanced options and xp_cmdshell enabled? What do you get for run_value for the following?

execute sp_configure 'show advanced options';

execute sp_configure 'xp_cmdshell';

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Lynn Hendricks (5/5/2015)
I've tried every possible solution found on the web to get xp_cmdshell working from within a stored proc, called from within an application. The current_user when the stored proc is running is an application role. The system_user is the person logged in, lets say me(while I'm testing), and I have sysadmin rights(which is really puzzling). I get the execute permission denied message. I've setup the proxy account - a windows account, set up a local user in the master, with execute permissions on xp_cmdshell, then tried impersonating that user. Get the principle cannot be impersonated. Yes, I tried with and without impersonating.
Added the same local user to that database, and to the securables on the application role. same error.
Tried every step I found on getting xp_cmdshell to work(when goggling) and cannot. What could I be missing? I cant get the application role a direct execute permissions on the extended stored procedure, anyone know of any work arounds, Or have had to use this type of process in the past.
Put in over 8 hrs trying every possibility, help!!!
thanks!!!

Try setting the owner of the database to "SA" (even if it's disabled... and it should be) and then setup the proc to execute as owner. Next, you have to grant the user permission to execute that stored procedure. Note that I did NOT say to grant the user privs to execute xp_CmdShell directly, which would be a huge security violation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Hendricks SSC Enthusiast Points: 142 More actions · Answer 3

Yes, it is configured, value is 1.

I do have all the basics covered.

Lynn Hendricks SSC Enthusiast Points: 142 More actions · Answer 4

Thanks for helping.

The owner of DB is SA, and yes SA is disabled.

I added the stored proc to the application role as execute permission. The one calling the xp_cmdshell.

I then added the statement to execute as owner to the proc. At first I thought it worked. Because when I executed it through the application, the errors did not come up. But the procedure didn't do anything. It didn't execute the command shell and create the file. I also have print statements within the proc, those usually pop up, displaying the values of the contents of my parameters. Those did not show. The file did not get created, that is suppose to during the execute xp_cmdshell.

No messages in any of the logs. Put a profiler trace on it, but that didn't give me any clue as to what is going on or getting done with the proc.

Any other ideas? I appreciate it.

Lynn Hendricks SSC Enthusiast Points: 142 More actions · Answer 5

Lynn Hendricks

SSC Enthusiast

Points: 142

May 6, 2015 at 8:02 am

#1795946

Oh, thank you, for helping.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

At this point, I'd recommend posting the DDL for the stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Hendricks SSC Enthusiast Points: 142 More actions · Answer 7

Well your suggestions worked.

After I was going to post the DDL, I noticed an error in the SQL statement.

And as I fixed that, the xp_cmdshell to create the file worked.

So now I need to figure out, out of all the steps I tried. To back out and then put in the basics.

And have the proc execute as owner, and give the proc execute permissions to the role that is running from the application.

Your steps worked. thank you so very much.

thanks again.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Lynn Hendricks (5/6/2015)
Well your suggestions worked.
After I was going to post the DDL, I noticed an error in the SQL statement.
And as I fixed that, the xp_cmdshell to create the file worked.
So now I need to figure out, out of all the steps I tried. To back out and then put in the basics.
And have the proc execute as owner, and give the proc execute permissions to the role that is running from the application.
Your steps worked. thank you so very much.
thanks again.

Thanks for the feedback, Lynn. Glad it's working. The first time I ever did it, it drove me nuts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Thanks, Jeff, for you help on this one. I was off yesterday and nowhere near a computer.

Lynn, glad to hear it's working.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions