Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Using xp_cmdshell can be quite helpful and sometimes possibly the only answer to some scenarios. I've read some posts on the internet that enabling xp_cmdshell might jeopardize the security of the database/server.

My question is that, is there anything we can do to reduce the risk? For example can we set some restrictions/applying users role, etc providing safeguards to mitigate the risk?

Thanks.

share|improve this question
2  
I am not with you on the following: "possibly the only answer to some scenarios". If you share your scenarios, you might get solutions not involving xp_cmdshell. We never use it in our systems. –  AlexKuznetsov Feb 13 '13 at 1:48
add comment

2 Answers

up vote 6 down vote accepted

Well, you could not grant execute on it explicitly to end users (deny it even), and only enable it in stored procedures that use EXECUTE AS with some login that does have execute permissions. Then grant execute on only that stored procedure to the user that needs to run the command.

First, make sure xp_cmdshell is enabled for the instance:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

Now depending on operating system and SQL Server service account, you may need to set up a proxy account (and to do this, thanks to UAC, you may need elevation and launch SSMS as an administrator):

EXEC master..sp_xp_cmdshell_proxy_account 'Domain\User', 'Password';

Then create a wrapper in your database that does whatever you need it to do (this is just a sample of a VERY generic wrapper that doesn't really protect you from anything; only using it to demonstrate):

USE yourdb;
GO
CREATE PROCEDURE dbo.uxp_cmdshell
  @cmd VARCHAR(2048)
WITH EXECUTE AS OWNER
AS
BEGIN
  EXEC master.dbo.xp_cmdshell @cmd;
END
GO

Now give your user or role permissions to execute the procedure:

GRANT EXECUTE ON dbo.uxp_cmdshell TO [your_user_or_role!];

Now when your_user logs in, and tries to execute willy-nilly calls to xp_cmdshell:

EXEC master.dbo.xp_cmdshell 'dir c:\';

They will get:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

However if they pass the same command to your new procedure:

EXEC dbo.uxp_cmdshell 'dir c:\';

It will work just fine (assuming your proxy account is set up correctly and/or the SQL Server service account has adequate permissions).

This can take some work, but effectively this allows you to control exactly what your users can do with xp_cmdshell.

share|improve this answer
    
Thanks. It seems what I was looking for. Could you please shed some light on how to do that? Do you mean I should enable xp_cmdshell in the stored proc where it's been called before using and then disable it right after the calling statement (when I'm done with that)? –  Nazila Beikpour Feb 12 '13 at 23:33
    
No, that tactic would not work - what if one person starts the procedure, enables xp_cmdshell, and before they call it, a different user had called the stored procedure and already hit the point where they disable it? –  Aaron Bertrand Feb 12 '13 at 23:35
    
I see your point Aaron. You're right. –  Nazila Beikpour Feb 13 '13 at 2:46
add comment

You can also setup a proxy account using sp_xp_cmdshell_proxy_account to allow non-administrators to use xp_cmdshell. This will allow you to setup a less privileged Windows account rather than xp_cmdshell always using the SQL Server service account. This is similar to setting up proxy accounts for SQL Agent jobs.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.