Allowing non-sysadmin user access to xp_cmdshell via Certificates

Question

Post reply

Allowing non-sysadmin user access to xp_cmdshell via Certificates

jellybean

SSCrazy

Points: 2014
More actions
February 17, 2023 at 6:57 pm

#4151155

Hi folks,
Hope you can help me with this one. I'm trying figure out how to add a non-sysadmin user access to xp_cmdshell (ultimately contained in a sproc). I have to use Certificates as its recommended in my environment.
Ultimately, we can associated the cert with the sproc that the user runs and let them run it.
I'm broadly following the guide here:
https://stackoverflow.com/questions/2016669/getting-execute-permission-to-xp-cmdshell/49139381#49139381
As a quick test I'm emulating a pre-exsting non-sysadmin user:
EXECUTE AS LOGIN = 'Domain\aUser';
SELECT SUSER_NAME(), USER_NAME();
declare @bcpCommand nvarchar(4000) = 'dir *.*'
exec master..xp_cmdshell @bcpCommand
But get the following error:
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
I have done the following:
--1. Enable the xp_cmdshell procedure
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
--2. Create a login 'Domain\RunxpCmdShellLogin' (windows user) for the non-sysadmin user that has public access to the master database. Done through user mapping
Done, I can see the user "RunxpCmdShellLogin" under the Securtity tab but am unsure how to check user has public access to the master DB...(suggestions welcomed)
--3. Grant EXEC permission on the xp_cmdshell stored procedure:
GRANT EXECUTE ON xp_cmdshell TO RunxpCmdShellLogin
I checked using: EXEC sp_helprotect 'xp_cmdshell' and it's there...
--4. Create a proxy account that xp_cmdshell will be run under using sp_xp_cmdshell_proxy_account
create credential ##xp_cmdshell_proxy_account## with identity = 'servername\XPCmdShellProxy', secret = 'somepassword'
I checked using: use master; select * from sys.credentials and everything looks okay
--5. There is a pre-existing cert called: "RunBcpCommandCert" that is associated with the sproc that contain the xp_cmdShell command. However when I ru nthe test above the same error is thrown.
I'm not sure how to interrogate this cert and see is it well formed and associated with the right things. I'm a bit "gappy" in my knowledge here and would appreciate some advice/suggestions/diagnostics and/or perhaps a bit of hand holding...
Thanks in advance. J.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

This shows how to execute xp_cmdshell without being a sysadmin.

https://sqland.wordpress.com/2018/10/11/allow-a-user-to-execute-xp_cmdshell-without-being-a-sysadmin/

To avoid giving your user access to the master db, as above, you will have to look at giving the rights to the certificate user and sign a sp with the same certificate. This is quite complicated but try reading sections 4 and 5 of the following:

https://www.sommarskog.se/grantperm.html#certsigndb

I suspect this will take a bit of trial and error to get it to work. It might be better to use the CLR or offload the whole process to the middle tier.

jellybean SSCrazy Points: 2014 More actions · Answer 2

Thanks for the links (esp. the first one).

I made more progress and also found this script here to be immensely useful: https://dba.stackexchange.com/questions/201776/grant-user-from-my-database-to-execute-xp-cmdshell

On the server itself in control panel --> Local Security Policy --> Users Rights Assignment --> I enabled "Log on as a Batch Job" which moved things forward a lot (got this from your 1st link).

However, when I test by running:

EXECUTE AS LOGIN = 'Domain\typicalUser' and run the sproc in question (that contains the xp_cmdshell command but has the applicable certificate associated with it)

The error I get now is:

SQLState = 28000, NativeError = 18456Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'SERVERNAME\XPCmdShellProxy'

Lots about this error online. I logged into the server box and checked the proxy account by running:

runas /user:SERVERNAME\XPCmdShellProxy cmd.exe and it accepted the password I entered so its not that...

The sproc in question is using xp_cmdShell as mentioned before and also the bulk copy command (BCP) to write out the server. It works for me but I'm a sysadmin.

I wrote new simplified, sproc (and signed it with the cert, and ran as myself and as a typical user) just calling the xp_cmdshell as follows:

declare @command nvarchar(4000) = 'dir *.*'

exec master..xp_cmdshell @Command

and yes it works so that's really great. However when I use the xp_cmdsheel to invoke the BCP (bulk copy porgram) command it does not work with the user. It works as myself.

I replace the -T flag in the BCP command body with -U and -P for username and password and explicitly called the XPCmdShellProxy with both myself and a typical user. Same error both times.

Hence, my conclusion is that is has to be some sort of other setting for the XPCmdShellProxy user. As mentioned before I added the user to "Log on as a Batch job" so perhaps "Take owner of files or other objects" or even "Enable computer and user accounts to be trusted for delegation" will assist?...Any ideas anyone?

Comments/suggestions much, much appreciated

J.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

Have you made the poxy with sp_xp_cmdshell_proxy_account?

Personally I would not call BCP with xp_cmdshell.

jellybean SSCrazy Points: 2014 More actions · Answer 4

Thanks for the speedy reply Ken.

I ran the the following command (as sysadmin) and got:

EXEC sp_xp_cmdshell_proxy_account 'SERVERNAME\XPCmdShellProxy', 'somePassword';

but got the error:

"An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: 5(Access is denied.), Error Status: 0."

So I ran:

create credential ##xp_cmdshell_proxy_account## with identity = 'SERVERNAME\XPCmdShellProxy', secret = 'somePassword'

which appeared to work as I can see it using this below (and it looks well formed):

use master

select * from sys.credentials

Concerning the BCP command, to date we sysadmins call it using xp_cmdShell inside a sproc and direct the results out to flat file on the server. It has been working well but this is the first time we want non-sysadmins to be able to call it. Hence all the stuff on proxy accounts and Certs....

I'm unsure how else to call it to be honest (or if that would achieve what we need it to do as there is more complexity here).

Hence I would like to exhaust the Cert/xp_CmdShell approach first and hence am really appreciative of any suggestions/observations you may be able to make...

Thank in anticipation,

J.

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 5

Just to be sure....

For the love of all that is holy in SQL Server, do not give individual users privs to execute xp_CmdShell in an unsupervised manner. Have them write stored procedures and you deploy those for them. Then, give them privs to execute the stored procedures. Make sure that you also make the code bullet-proof against "CmdLine" or "DOS" Injection.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ken McKelvey SSCoach Points: 18842 More actions · Answer 6

jellybean wrote:

I'm unsure how else to call it to be honest

If you do not like Powershell etc then maybe try putting the SP in an agent job and then allow the user to start the job as detailed below:

https://sqlstudies.com/2019/04/15/how-do-i-let-someone-execute-a-sql-agent-job-they-dont-own/

Even better, have step 1 do the BCP from the command prompt and then have step 2 do whatever else the sp does.

It looks a lot easier than messing around with proxies and certificates.