Stefan Zvonar

A collection of articles about software development, data analysis and other technical tidbits

Executing xp_cmdshell with SQL Server 2008 R2

Posted by Stefan Zvonar on November 18, 2011

Sometimes there comes a time when you would like to have a user of your database be able to execute a program by making use of SQL Server’s xp_cmdshell procedure.  It can be a little tricky setting up the permissions to enable this call, so here are some steps you can take to make it possible.

Firstly, allow execution of xp_cmdshell:

USE [master]
GO

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Secondly, create a SQL Login that can run xp_cmdshell.  Please create a more secure password than the example given:

USE [master]
GO
CREATE LOGIN [CommandShellLogin] WITH PASSWORD ='abc123!@#'
GO
CREATE USER [CommandShellLogin] FROM LOGIN [CommandShellLogin]
GO
GRANT EXECUTE ON xp_cmdshell TO [CommandShellLogin]
GO

Thirdly, because you will be switching to this command shell login temporarily, grant impersonation rights to the user’s group. Note: Am assuming that the user group is mapped to a login in SQL Server:

USE [master]
GO
GRANT IMPERSONATE ON LOGIN::CommandShellLogin TO [DOMAINUSER GROUP]
GO

Now, you could make [CommandShellLogin] a member of sysadmin to make this work, but that would be providing too much priviledges. Instead, you can set up a proxy account that xp_cmdshell uses whenever it is called. This will be an actual windows account that the command will execute as, so make sure that the windows account has the permissions to perform whatever it is you are intending it to execute:

EXEC sp_xp_cmdshell_proxy_account 'DOMAINUser', 'UserPassword'
GO

Now in your procedure, trigger, script or whereever you plan on running xp_cmdshell, switch the context to the CommandShellLogin user temporarily to execute your desired command. Here we are just running DIR as an example:

EXECUTE AS LOGIN = 'CommandShellLogin'
EXEC xp_cmdshell 'DIR'
REVERT

Note: Setting up the proxy account above results in any xp_cmdshell to run as the user specified. If you no longer want subsequent calls to xp_cmdshell to run as that windows account, then ensure that you empty the proxy account so that it does not run anymore as that account:

EXEC sp_xp_cmdshell_proxy_account NULL

Hope this helps,

Stefan.

-
stefanzvonar.com – A collection of articles on computer programming, software development, data analysis and other technical tidbits.
-

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: