Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to run xp_cmdshell via .NET App using non SYSADMIN role Expand / Collapse
Author
Message
Posted Sunday, February 23, 2014 11:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:13 AM
Points: 14, Visits: 49
Dear masters,

I have a stored procedure (SP) that contains "EXEC xp_cmdshell eror msg >>D:\logfile.txt, no_output"

Whenever i call/run the SP, via .NET App and SSMS directly, using user that has (Server) Login Properties --> Server Role: SYSADMIN, it works.

But, when I run using other user (that doesn't have the SYSADMIN role) it only works when executed via SSMS only. If i execute via .NET App, although this user has (Server) Login Properties --> User Mapping: DB_DDLADMIN, it doesnt work.
There is error "The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'."

How can I run this xp_cmdshell without having SYSADSMIN role ?

thanks
Post #1544362
Posted Monday, February 24, 2014 5:50 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 12,905, Visits: 32,157
yocki (2/23/2014)
Dear masters,

I have a stored procedure (SP) that contains "EXEC xp_cmdshell eror msg >>D:\logfile.txt, no_output"

Whenever i call/run the SP, via .NET App and SSMS directly, using user that has (Server) Login Properties --> Server Role: SYSADMIN, it works.

But, when I run using other user (that doesn't have the SYSADMIN role) it only works when executed via SSMS only. If i execute via .NET App, although this user has (Server) Login Properties --> User Mapping: DB_DDLADMIN, it doesnt work.
There is error "The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'."

How can I run this xp_cmdshell without having SYSADSMIN role ?

thanks


te recommended way is to have your stored procedure signed by certificate, which allows the proc to execute under enhanced credentials; Tutorial: Signing Stored Procedures with a Certificate

A band-aid is to create a sysadmin user, and use EXECUTE AS that superuser in the procedure.

The worst thing to do, but it is possible, is to grant execute on xp_cmdshell to your user.

aside from that, it's doubtful you really need xp_cmdshell usage in the first place. if you just need to write to a file, there ar elots of ways to do that, including CLR procs, writing to a table and having a job write it out via powershell, and so many other possibilities.

what, exactly are you doing with the command line?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1544456
Posted Monday, February 24, 2014 8:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 35,342, Visits: 31,875
The certificate method is a real PITA and mostly unnecessary. If you write a stored procedure that does the work and include EXECUTE AS OWNER and the database is owned by a login having SA privs (hopefully, the owner is "SA" and that account is disabled), then the app won't need any privs except privs to run the stored procedure.

Whatever you do, do NOT give the app privs to execute xp_CmdShell directly.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1544544
Posted Monday, February 24, 2014 9:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:13 AM
Points: 14, Visits: 49
Hi Lowell,
Hi Jef Moden,

Thanks for the reply...

What I am trying is to create a text file containing a message why the insert process failed. Here is a bit of the pseudocode:

IF condition1 = 0
BEGIN
xp_cmdshell write to a file "this value is 0"
END
IF condition <>0
BEGIN
INSERT INTO tabel
END

This stored procedure is actually executed by an internal .NET application. Actually I want to avoid granting execute to xp_cmdshell, BUT i have no idea how this application can capture the feedback from the SP that has been executed so I use cmdshell to pool the feedback. This is actually what i want to do:

IF condition1 = 0
BEGIN
set @erorlog = @erorlog + "this value is 0"
END

I want the application to be able to read this "@erorlog" variable. How can i achieve that ?

I do not want to create a new table just to pool the error messages.

Also, I kinda know the danger of this (http://stackoverflow.com/questions/3797036/sql-server-why-xp-cmdshell-is-disabled-by-default) but my users are really really just operator. They have the very most basic skill of SQL (even dont have at all).
So, you think its still save to give this particular user access to xp_cmdshell ?

Thanks alot
Post #1544757
Posted Monday, February 24, 2014 9:22 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 35,342, Visits: 31,875
yocki (2/24/2014)
Hi Lowell,
Hi Jef Moden,

Thanks for the reply...

What I am trying is to create a text file containing a message why the insert process failed. Here is a bit of the pseudocode:

IF condition1 = 0
BEGIN
xp_cmdshell write to a file "this value is 0"
END
IF condition <>0
BEGIN
INSERT INTO tabel
END

This stored procedure is actually executed by an internal .NET application. Actually I want to avoid granting execute to xp_cmdshell, BUT i have no idea how this application can capture the feedback from the SP that has been executed so I use cmdshell to pool the feedback. This is actually what i want to do:

IF condition1 = 0
BEGIN
set @erorlog = @erorlog + "this value is 0"
END

I want the application to be able to read this "@erorlog" variable. How can i achieve that ?

I do not want to create a new table just to pool the error messages.

Also, I kinda know the danger of this (http://stackoverflow.com/questions/3797036/sql-server-why-xp-cmdshell-is-disabled-by-default) but my users are really really just operator. They have the very most basic skill of SQL (even dont have at all).
So, you think its still save to give this particular user access to xp_cmdshell ?

Thanks alot


Like I said, I wouldn't grant xp_CmdShell privs to any user (login, etc). I would build a stored procedure that does only what is need with xp_CmdShell, include EXECUTE AS OWNER in the proc, and the grant the user privs to only run the stored proc.

NEVER grant anyone or any login privs to run xp_CmdShell (SA's will be the obvious exception). Always do it through stored procedures.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1544759
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse