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
USE MASTER
GO
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'
GOxp_cmdshell 'whoami.exe'
REVERT
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
