How do you secure the SQL Server instance after enabling xp_cmdshell ?

OR
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


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