4

So I have a bunch of database users who should be able to execute a procedure with a call to xp_cmdshell. They are not windows domain accounts, they are just sql-server logins. I want to do it using a proxy ##xp_cmdshell_proxy_account##. I am aware of security threats that it may bring. I cannot use 'WITH EXECUTE AS admin' due to our company inner version control and I obviously do not want to grant all of the users rights to execute xp_cmdshell, that is why my only choice (am I right here?) is using the proxy account.

I have a special windows-domain account for it, to whom I granted xp_cmdshell execution rights. I can successfully execute cmdshell using this account. I created the proxy account using the following command:

EXEC sp_xp_cmdshell_proxy_account  [my_domain\special_account],'SuperSecretPassword'

When I try to execute any xp_cmdshell command as a database user with no xp_cmdshell rights an error appears:

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

My question is - can I execute a xp_cmdshell command using an sql server login and I am simply missing something here or is it only possible using a domain account? Is there any other procedure to follow here to make it happen?

If I fail to make the above work I will have to switch some logic to a CLR procedure, but I am determined to make it work.

I am using sql-server 2012. Any help will be appreciated, thanks!

1

It appears you need to grant access to xp_cmdshell for your sql server login. Once the user has permission to run xp_cmdshell, it will use your proxy account to run it since it is a non-privileged user.

Another alternative would be to use certificates to sign your stored procedures, allowing you to grant additional privileges for users executing that stored procedure.

https://msdn.microsoft.com/en-us/library/bb283630.aspx

The downside of certificates is that the procedure needs to be re-signed every time you make a change to it, which can be a pain.

3

You can use a SQL Server login.

The following procedure is explained in MSDN:

To allow non-administrators to use xp_cmdshell, and allow SQL Server to create child processes with the security token of a less-privileged account, follow these steps:

  1. Create and customize a Windows local user account or a domain account with the least privileges that your processes require.
  2. Use the sp_xp_cmdshell_proxy_account system procedure to configure xp_cmdshell to use that least-privileged account. Note You can also configure this proxy account using SQL Server Management Studio by right-clicking Properties on your server name in Object Explorer, and looking on the Security tab for the Server proxy account section.
  3. In Management Studio, using the master database, execute the GRANT exec ON xp_cmdshell TO '' statement to give specific non-sysadmin users the ability to execute xp_cmdshell. The specified login must be mapped to a user in the master database.
1

The question is: what exactly are you using xp_cmdshell for? Is it to run a random program, or get a directory listing, or something else? You seem to be avoiding SQLCLR but you don't say why. In many cases, SQLCLR is more secure and more efficient. But it is not possible to be more specific unless additional details are provided regarding what the ultimate goal is.

Outside of that, you can grant permissions in a highly targeted manner using signatures (@Shane also mentioned this).

You can find an example of doing this in my answer to the following question, also here on DBA.StackExchange:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

The basic concept is that you will:

  1. Create a Certificate in the master Database
  2. Create a Login based on this Certificate
  3. Grant this Login the appropriate permission to run xp_cmdshell
  4. Backup the Certificate
  5. Restore the Certificate into the Database that has the Stored Procedure that is calling xp_cmdshell
  6. Sign the Stored Procedure that is calling xp_cmdshell via ADD SIGNATURE

Additional links to other variations of examples can be found in my answer to this question:

stored procedure can select and update tables in other databases - minimal permissions granted

  • The signature looks really safe and promising, although in my case it can be problematic, since we use a script common for a few databases to create users based on a table in case of a restore and etc. so it would mean implementing a lot of new logic totally useless for other databases. I use xp_cmdshell to create a directory. Then I actually use a SQLCLR to create the file in that directory. I've asked this question to understand how to use the proxy account itself and learn about alternatives other than SQLCLR. Considering what I know now, I should switch to SQLCLR. Thanks! – PacoDePaco Aug 10 '16 at 6:19
  • @PawełKucharski I don't understand the common script and why this would be a problem. But regardless, if you are already using SQLCLR to create a file, then why use something else to create the directory? It's just one line of code to add to the file creation object to create the directory. And even if this was successful, would you be able to create the file using xp_cmdshell? If you want to learn more about SQLCLR, see a series of articles I am writing on it: Stairway to SQLCLR. Also see SQL# which I created. – Solomon Rutzky Aug 10 '16 at 8:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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