Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to
Contact the author
for this blog
I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.
- Posted on 22 March 2012
How do you allow non-administrator to use xp_cmdshell ?
From the day I started working with SQL server, I have heard
about the security risk associated with enabling the xp_cmshell and used to restrain from using the xp_cmdshell. Now I have
started using it as xp_cmdshell simplify many tasks for us.The security risk associated with xp_cmdshell can be reduced by following the steps given below.
By default the xp_cmdshell is disabled and this can be enabled by surface area configuration manager or sp_configure.Enabling the xp_cmdshell is a risk as it open a windows command shell with same security context of the SQL server service account. In most of the scenario SQL server service account will have elevated permission like member of local/domain administrator group.Using this elevated rights, malicious users can do lot of damage like creating a log in account with administrator right ,changing the registry,deleting file/folders,accessing network paths,stealing data,etc. With default configuration, SQL login need sysadmin or control server right to run the xp_cmdshell command.
The first step to reduce the security risk of enabling xp_cmdshell is replacing the SQL service account with minimal rights.It is always advised to run the SQL server and related services under the domain account with minimum privilege. This will helps to reduce the risk of accessing the xp_cmdshell by SQL login with sysadmin or control server rights.
To allow non-administrator to run the xp_cmdshell, we have allow SQL server to open windows command shell with the security context of a less privileged windows account by configuring server proxy account. To do that create a domain account/local machine account with very minimal permission and configure the proxy account as given below
EXEC sp_xp_cmdshell_proxy_account 'MyDomain\My.login','MyPassw0rd'
Now the non-administrator can run the xp_cmdshell command by creating a user for them in master database and granting the execute permission to xp_cmdshell in master database as given below
CREATE login cmdshelluser WITH password ='Password123'CREATE USER cmdshelluser FOR login cmdshelluser GRANT EXECUTE ON xp_cmdshell TO cmdshelluser
Now the windows command shell opened by cmdshelluser using the xp_cmdshell will have the security context of the proxy account configured earlier. To determine security context
EXECUTE AS LOGIN = 'cmdshelluser'
If non-administrators granted with execute permission on xp_cmdshell ran the xp_cmdshell before configuring the proxy account, SQL server will throw below error
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
Thank you for reading this article. Follow my blog @ Facebook page
Leave a comment on the original post
[www.practicalsqldba.com, opens in a new window]