Running xp_cmdshell under non system admin

Question

Running xp_cmdshell under non system admin

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
April 9, 2012 at 8:16 am

#253758

I am getting following error while running xp_cmdshell under non system admin rights.
An error occurred during the execution of xp_cmdshell. A call to 'CreateProcessAsUser' failed with error code: '1314'.
any help will be appreciated..

Viewing 15 posts - 1 through 15 (of 20 total)

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

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

So you want to set up xp_cmdshell to work for non-admin users? This page shows how to do it using sp_xp_cmdshell_proxy_account:

http://msdn.microsoft.com/en-us/library/ms175046.aspx

_________________________________
seth delconte
http://sqlkeys.com

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

I tried same here but getting an error. I think this is something related to security policy..

USE master

GO

-- Create the SQL login which will use xp_cmdshell.

CREATE LOGIN CmdShellUser WITH PASSWORD='123'

-- Create a proxy credential for xp_cmdshell assigned to a Domain/Windows account.

EXEC sp_xp_cmdshell_proxy_account 'SUTTER-CHS\_SC_1SQL', 'plmokn';

-- Grant database access to the SQL Server login account that you want to provide access.

EXEC sp_grantdbaccess 'CmdShellUser'

-- Grant execute permission on xp_cmdshell to the SQL Server login account.

GRANT exec ON sys.xp_cmdshell TO CmdShellUser

GO

EXECUTE AS login = 'CmdShellUser'

EXEC xp_cmdshell 'DIR C:\*.*'

revert

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736150

April 9, 2012 at 12:29 pm

#1470845

How did you set up the proxy?

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 4

Hi Steve,

I have not setup the proxy because in our environment xp_cmdshell command is used by lots of stored procedure so setting up credentials is enough. Correct me if I am wrong, I think if we run as sql job then we need proxy setup.

I created credentials using following account.

EXEC sp_xp_cmdshell_proxy_account

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 5

Did you follow a similar procedure as what was outlined in Seth's link?

Just trying to figure out where things might have gone wrong. You should use a proxy, but it can be tricky to set up

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 6

Yes, I did.

Looks like to me that some group policy is setup on this box to restricted xp_cmdshell.

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 7

Does this help? http://support.microsoft.com/kb/248391/en-us

The service account needs to have these permissions to create the user that is the proxy.

It could be policy of some sort. I'd think PBM would throw a different error, but possibly some GP from the domain is in place.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 8

Actually, this is part I am still working on.. Thanks a lot..

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 9

I would NEVER give a login for a human or application the privs necessary to execute xp_CmdShell directly. In fact, I'd never give a human or application more than PUBLIC privs. That, notwithstanding, you can setup to allow a user (human or app) the privs to execute a stored procedure which contains a call to xp_CmdShell without that user having the privs necessary to run xp_CmdShell directly.

I've got a nice script to demonstrate all of this at home. I'll try to remember to post it tonight.

--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)

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 10

Thanks Jeff,

How about doing like this ?

CREATE PROCEDURE dbo.usp_ExecCmdShellProcess

AS

BEGIN

DECLARE @job NVARCHAR(100) ;

SET @job = 'xp_cmdshell replacement - ' + CONVERT(NVARCHAR, GETDATE(), 121) ;

EXEC msdb..sp_add_job @job_name = @job,

@description = 'Automated job to execute command shell script',

@owner_login_name = 'sa', @delete_level = 1 ;

EXEC msdb..sp_add_jobstep @job_name = @job, @step_id = 1,

@step_name = 'Command Shell Execution', @subsystem = 'CMDEXEC',

@command = 'dir c:\', @on_success_action = 1 ;

EXEC msdb..sp_add_jobserver @job_name = @job ;

EXEC msdb..sp_start_job @job_name = @job ;

END ;

GO

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 11

balbirsinghsodhi (4/9/2012)
Thanks Jeff,
How about doing like this ?
CREATE PROCEDURE dbo.usp_ExecCmdShellProcess
AS
BEGIN
DECLARE @job NVARCHAR(100) ;
SET @job = 'xp_cmdshell replacement - ' + CONVERT(NVARCHAR, GETDATE(), 121) ;
EXEC msdb..sp_add_job @job_name = @job,
@description = 'Automated job to execute command shell script',
@owner_login_name = 'sa', @delete_level = 1 ;
EXEC msdb..sp_add_jobstep @job_name = @job, @step_id = 1,
@step_name = 'Command Shell Execution', @subsystem = 'CMDEXEC',
@command = 'dir c:\', @on_success_action = 1 ;
EXEC msdb..sp_add_jobserver @job_name = @job ;
EXEC msdb..sp_start_job @job_name = @job ;
END ;
GO

Heh... looks like something I wrote for someone a year ago. That's fine but let me ask some questions.

1. Why do you recreate the job as a self deleting job each time?

2. Where is the output going to go?

3. The code above will run asynchronously. You'll need to check in code to make sure the job completed which probably means a WAIT FOR DELAY bit of code. Can your code withstand that?

4. Someone is going to have to have some pretty good privs to run the code above because it makes a job. Are you prepared to give such privs to the users? I'm not sure I would be.

--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)

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 12

Here's one of many solutions available as of SQL Server 2005 where the user only has EXECUTE privs on a stored procedure that uses xp_CmdShell and the user can NOT use xp_CmdShell directly. In fact, except for the EXECUTE privs on just the proc, the user has ONLY PUBLIC privs. The code includes all of the knowledge you'll need to set all of this up except for how to create a simple Windows user on the Windows Server... and that's like falling off a slippery log.

--===== Make sure none of the test objects I use exist ahead of time so that we can see that this all actually works

SELECT '****************************** Making sure the things we need don''t already exist. ******************************';

USE MASTER;

DROP DATABASE MyTester; --BE REAL CAREFUL HERE!!! Drops the database I tested against

EXEC sp_xp_cmdshell_proxy_account NULL; --Drops the cmd shell proxy just to be sure.

DROP USER [yourdomainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.

DROP LOGIN [yourdomainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.

DROP USER [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.

DROP LOGIN [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Recreate my test database and the user which only has "public" privs.

-- I believe the DEFAULT_SCHEMA is important here.

SELECT '****************************** Creating [MyTester] DB and TestDummy. ******************************';

CREATE DATABASE [MyTester];

GO

USE [MyTester];

CREATE LOGIN [yourdomainname\TestDummy] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTester], DEFAULT_LANGUAGE=[us_english];

CREATE USER [yourdomainname\TestDummy] FOR LOGIN [yourdomainname\TestDummy] --This just maps the database for the user

GO

--===== This just displays how limited the TestDummy user is

EXEC sp_helpuser [yourdomainname\TestDummy];

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Now we build the Login and proxy account using the SqlCmdUser I built in Windows on my box at home.

-- IMPORTANT!!! A step we cannot skip is that we have to build a user from the SqlCmdUser login.

-- NOTE THAT THIS MUST BE A SINGLE USER AND NOT A WINDOWS GROUP!

SELECT '****************************** Building/Granting Proxy user stuff. ******************************';

USE [master];

CREATE LOGIN [yourdomainname\SqlCmdUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

CREATE USER [yourdomainname\SqlCmdUser] FOR LOGIN [yourdomainname\SqlCmdUser] WITH DEFAULT_SCHEMA=[dbo];

EXEC sp_xp_cmdshell_proxy_account 'yourdomainname\SqlCmdUser','SqlCmdUser';

--===== Very important here... we have to grant access to xp_CmdShell to the new Window's user...

GRANT EXECUTE ON xp_CmdShell to [yourdomainname\SqlCmdUser];

GO

--===== This just displays how limited even the SqlCmdUser is!!!!

EXEC sp_helpuser [yourdomainname\SqlCmdUser];

GO

-----------------------------------------------------------------------------------------------------------------------

-- ********** NOTE THAT EVERYTHING ABOVE IS AS WE HAD IT BEFORE! **********

-- ********** NOTE THAT THE ONLY THING WE HAVE TO DO IN THE STORED PROCS (SEE BELOW

-- ********** IS TO INCLUDE "WITH EXECUTE AS OWNER"

-- heh... And Bob's your Uncle!

-----------------------------------------------------------------------------------------------------------------------

--===== Create a stored procedure in the new "MyTester" database that uses xp_CmdShell.

-- Keep in mind that, right now, we're signed in as a member of "dbo".

USE [MyTester];

GO

DROP PROCEDURE dbo.GetDirInfo;

GO

CREATE PROCEDURE dbo.GetDirInfo

WITH EXECUTE AS OWNER

AS

EXEC xp_cmdshell 'DIR C:\';

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

;

GO

--===== Give the general puplic privs to run the sproc.

GRANT EXECUTE ON dbo.GetDirInfo TO PUBLIC

;

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Now, show that the "TestDummy" user can execute the proc but not xp_cmdshell itself.

-- Simulate logging in as a user with low privs...

EXECUTE AS LOGIN = 'yourdomainname\TestDummy'

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

-- This works... (which is what we want)

PRINT REPLICATE('=',80);

PRINT '********** Testing execution of dbo.GetDirInfo **********'

EXEC dbo.GetDirInfo

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

-- This doesn't... (which is also what we want)

PRINT REPLICATE('=',80);

PRINT '********** Testing execution of xp_CmdShell directly **********'

EXEC xp_cmdshell 'DIR C:\'

;

GO

--===== Test complete... go back to normal.

REVERT

;

--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)

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 13

Thanks Jeff for the script.. I ran the script but still getting the same error.

A call to 'CreateProcessAsUser' failed with error code: '1314'.

I am sure that this error related to the group policy setup on the box.

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 14

balbirsinghsodhi (4/10/2012)
Thanks Jeff for the script.. I ran the script but still getting the same error.
A call to 'CreateProcessAsUser' failed with error code: '1314'.
I am sure that this error related to the group policy setup on the box.

Did you make the necessary changes to the script before you ran it? Things like "yourdomainname" need to be changed to your actual domain name and you must have the windows users correctly setup on the Windows Server before you run the script.

--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)