THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Xp_cmdshell and permissions

This blog post was inspired from a newsgroup discussion. The question basically is:
What do you need to do in order to use xp_cmdshell?

Note that there are obvious security implications of doing this. (I'm not recommending usage of xp_cmdshell in general, this is a technical blog post!) We first need to think about what happens here, from an architectural level:

Somebody who has logged in to SQL Server executes xp_cmdshell. For this, SQL Server need to spawn a process in Windows. A process in Windows need to execute in a Windows user account.

So, what Windows account is used? If the SQL Server login who is executing xp_cmdshell is sysadmin, then SQL Server will use the service account (it will not "pretend to be somebody else"). But if the login isn't sysadmin, then we need to configure what Windows account to be used (using sp_xp_cmdshell_proxy_account). Note that this configuration is the same for all non-sysadmins!

But there's a little bit more to it. Below is an outline of what need to be done. Step 2 and 3 are only needed if the one who is to execute xp_cmdshell isn't sysadmin. Note that the steps don't have to be performed in the order listed below.

  1. We need to allow usage of xp_cmdshell in general (on 2005). Use "Surface Area Configuration" or sp_configure for this.
  2. We need to have a user in the master database which has execute permission on xp_cmdshell. If you are uncertain about the difference between logins and users, you should read up about it in BOL.
  3. We need to say what Windows account should be used when a non-sysadmin user is executing xp_cmdshell.

So, here's the TSQL script that does all above:

--1, allow xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

 

--2, grant permission to xp_cmdshell
USE master
CREATE LOGIN JohnDoe WITH PASSWORD = 'jlkw#.6('

--Note, we are in the master database!!!
CREATE USER JohnDoe FROM LOGIN JohnDoe

--Run as login x
EXECUTE AS login = 'JohnDoe'
--Below fails, no execute permission on xp_cmdshell
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT
GO

--Note, we are in the master database!!!
GRANT EXECUTE ON xp_cmdshell TO JohnDoe

--Try again
EXECUTE AS login = 'JohnDoe'
--Execution of xp_cmdshell is allowed.
--But I haven't configured the proxy account...
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT
GO

 

--3, specify the proxy account for non-syadmins
--Replace obvious parts!
EXEC sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd'
EXECUTE AS login = 'JohnDoe'
--Execution of xp_cmdshell is allowed.
--And executes successfully!!!
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT

 

--Cleanup
EXEC sp_xp_cmdshell_proxy_account null

DROP USER JohnDoe
DROP LOGIN JohnDoe
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE

 

Published Thursday, August 23, 2007 9:12 AM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Berzat said:

Wery nice and clean text. I like the "rollback" at the end :-)

September 6, 2007 9:52 AM
 

TonyZeng said:

In SQL Server 2005 Cluster, it seems that have some problem.

I always got the error information like below:

Msg 15121, Level 16, State 10, Procedure xp_cmdshell, Line 1

An error occurred during the execution of xp_cmdshell. A call to 'CreateProcessAsUser' failed with error code: '1314'.

Note: In this TSQL:

EXEC sp_xp_cmdshell_proxy_account 'domain\tonyzeng','pwd'

domain\tonyzeng belongs to the administrators group of this computer.

September 17, 2007 10:15 PM
 

TiborKaraszi said:

Most probably the service account for the SQL Server service (not the proxy account) don't have appropriate Windows priviliges to create a process as some other user. I prefer to change service account using "SQL Server Configuration Manager". Also, the priviliges needed are listed in Books Online.

September 18, 2007 2:08 AM
 

TonyZeng said:

Thank you, TiborKaraszi.

But the service account for the SQL Server service also belongs to  the administrators group of the computer.It's not enough,  still need other priviliges?

By the way, could you tell me what are the priviliges needed in Books Online? Thanks for your kindly help.

September 18, 2007 7:16 PM
 

TiborKaraszi said:

No, Administrator is not enough. Again, do yourself a favour and set the service account using the "SQL Server Configuration Manager" tool, which will make sure that the account has apropriate permissions.

Here's a Books Online URL that lists the permissions needed:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/309b9dac-0b3a-4617-85ef-c4519ce9d014.htm

September 24, 2007 4:14 AM
 

ravi said:

TiborKaraszi,

I have tried the steps outlined by you for the xp_cmdshell issue on two SQL 2005 servers. One one server i am able to use xp_cmdshell, while on the other server i keep getting the "A call to 'CreateProcessAsUser' failed with error code: '1314'.". Any advice?

Thank You

Ravi

September 26, 2007 2:19 AM
 

TiborKaraszi said:

I would still suspect that the SQL Server service account is lacking some of the needed priviliges. If you are confindent that this isn't the case, I recommend you open a case with MS Support.

September 26, 2007 2:51 AM
 

Berzat said:

Try to add the SQL Server service account in this windows group: SQLServer2005MSSQLUser$ComputerName$InstanceName...

September 26, 2007 4:34 AM
 

shahgols said:

I found Berzat's comment to work for me.  

December 12, 2007 7:51 PM
 

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM
 

Matthew said:

if you change the permissions to give the service account the ability to use xp_cmdshell you may have to wait until your server refreshes these settings, this takes 90 mins. Or you can restart. I think you need to allow 'Replace a process level token'.

April 21, 2008 10:09 PM
 

Rushikesh said:

I tried all the options suggested in above post but nothing is working for me.

AS SQL sever 2005 is running on windows sever 2003 which is need not required to restart in case of service account change, but tried restart sever option also ,but its not working and giving the same error A call to 'CreateProcessAsUser' failed with error code: '1314'.Please help. Thanks

Rrgards,

Rushikesh

July 22, 2008 1:36 AM
 

TiborKaraszi said:

That error message suggest that the SQL Server service account lacks some of the required permissions. Was there created a number of groups when you installed your instances, for instance on my machine for a default instance I have a group named "SQLServer2005MSSQLUser$TIBWORK$MSSQLSERVER". See if you have a s imilar and verify that the SQL Server service account is a member of this group.

July 22, 2008 2:01 AM
 

Rushikesh said:

Thanks lot for quick reply.

I add Server service account to all these groups. But facing the same error do I need to restart the server?

regards,

Rushikesh

July 22, 2008 2:33 AM
 

Glenn Koproske said:

I have to make this work on an application role name.  Our software project has the users log in and then the role takes effect.  The role apparently needs to execute xp_cmdshell, but I can't get the proxy account to accept the role name.

August 6, 2008 12:07 PM
 

Rowen Singh said:

Hi,

I'm trying to launch a multiple instances of an external exe from sql server 2005. But I don't want sql to wait for the exe to finish before it continues to the next launch

Is there a command besides xp_cmdshell that can accomplish this ?

or do I have to resort to using the built int ole features?

Thanks for your help in advance

August 8, 2008 5:19 AM
 

TiborKaraszi said:

Perhaps consider Service Broker with external activation? (There has been some improvements with extenral activation in 2008.)

August 8, 2008 7:22 AM
 

satish said:

HI i can execute exe on the same machine where sql server 2005 is present

Ex:  exec xp_cmdshell 'c:\services\test.exe'

How can i  run the exe on sql server when sql server & exe are on two different machines

pls help me

thanks

October 21, 2008 4:24 PM
 

TiborKaraszi said:

You need some utility to from one machine start process on another machine. This is an OS issue, and I'm sure there are plenty such utilities available "out there". :-)

October 22, 2008 2:21 AM
 

Sray said:

Hi,

I have followed these instructions and get to number 3 and then I get the following message:

Msg 15466, Level 16, State 2, Procedure sp_xp_cmdshell_proxy_account, Line 1

An error occurred during decryption.

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.

What did I miss?

Thank you,

 -S

December 11, 2008 5:52 PM
 

Nancy said:

Thank you so much

I am using BCP in a recurring job and did not want to use an administrator account.

I successfully set this up in a 3 step job.

1.  Set permissions ((grant Execute & Proxy Account setup) (use master)

2.  The job on my database (use my db)

3.  Revoke the proxy settings & grant (use master)

May 15, 2009 11:42 AM
 

Altaf said:

Your solution worked in case when BUITIN\ADMINISTRATORS are enabled. As per our secrutiy policy this group is disabled, could you please tell me some workaround in this situation.

August 30, 2009 4:27 AM
 

Ninad said:

Hello there,

I tried the above mentioned steps but i m getting following error:-

Cannot execute as the server principal because the principal "SomeUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

below is the SP code snipit.

ALTER PROCEDURE [dbo].[eet_p_GetEquipmentImages]

(

@QuoteID nvarchar(55),

   @ServerName nvarchar(150),

   @ImageLocation nvarchar(150),

@ImageType nvarchar(15)

)

AS

BEGIN

DECLARE @command as nvarchar(225)

DECLARE @URL as nvarchar(225)

DECLARE @EquipmentFolder nvarchar(150)

SELECT TOP 1 @EquipmentFolder=Product.ProductNumber FROM dbo.QuoteDetail --Product.Name

INNER JOIN Product ON  Product.ProductID = QuoteDetail.ProductID

WHERE quoteid = @QuoteID

SET @Command = 'DIR /B \\' + @ServerName + '\' + @ImageLocation + '\' + @EquipmentFolder + '\' + @ImageType

SET @URL = 'http://' + @ServerName + '/' + @ImageLocation + '/' + @EquipmentFolder + '/'

TRUNCATE TABLE eet_EquipmentImagesTempTable

EXECUTE AS login = 'SomeUser'

INSERT INTO eet_EquipmentImagesTempTable EXEC xp_CmdShell @Command

REVERT

SELECT @URL + FileName as [FileName] FROM eet_EquipmentImagesTempTable WHERE FileName NOT LIKE '%The system cannot find the file specified.%'

END

SomeUser also has dbowner rights so that to insert data in eet_EquipmentImagesTempTable

This is wat i did to create SomeUser & to set the proxy user

USE master

CREATE LOGIN EETUser WITH PASSWORD = 'abc@123'

CREATE USER EETUser FROM LOGIN SomeUser

GRANT EXECUTE ON xp_cmdshell TO SomeUser

EXEC sp_xp_cmdshell_proxy_account 'resource\user1','somepassword'

user1 is a domain user & a local admin on server & is also a sysadmin

m i missing anything here? Appreciated you help. Thanks.

February 24, 2010 7:27 PM
 

Ninad said:

CORRECTION:-- HERE I have used 'SomeUser' every where instead of (EETUser)

USE master

CREATE LOGIN SomeUser WITH PASSWORD = 'abc@123'

CREATE USER SomeUser FROM LOGIN SomeUser

GRANT EXECUTE ON xp_cmdshell TO SomeUser

EXEC sp_xp_cmdshell_proxy_account 'resource\user1','somepassword'

February 24, 2010 7:30 PM
 

Jesse said:

I have a different issue.  I am running xp_cmdshell and do not have any permission issues.  It runs fine.  What I have found though is it is not running under the current user, it is instead running as the SQL Server account.  I need it to run under the user who is executing it.  The problem I have is I cannot use a EXECUTE AS statement becuase my users are added as Domain groups (like GROUP1).  So if I say ExECUTE as DOMAIN\USER it says cannot find becuase DOMAIN\USER is not a login (DOMAIN\GROUP1 is).  If I do a suser, i see USER not GROUP so I know SQL can pick it up.  The proc shows it is executed as USER but the output from xp_cmdshell shows it was executed as SQLSERVERUSER (the user who runs the sql service).  

November 30, 2010 9:59 AM
 

sage said:

Everything was ok til step 3! When i run step 3 it says something like this:

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: '0'.

The account was ok but SQL stil reporting error... so I create crdental manualy with command:

create credential ##xp_cmdshell_proxy_account## with identity = 'SERVERNAME\useracount', secret = '42342eddds#'

(i m not on a domain, and servername mast be inculded because if you  not you will same error that windows user is not valid!!!)

next in Microsoft SQL Server Menagment studio  go to server Agent  an in proxies define new proxi

in General

proxy name: some name

 credetials: clisk on  ... button and select  

##xp_cmdshell_proxy_account##

in Principals:

clisc add button and select User  click, OK..

and now run:

EXEC sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd'

EXECUTE AS login = 'JohnDoe'

--Execution of xp_cmdshell is allowed.

--And executes successfully!!!

EXEC xp_cmdshell 'DIR C:\*.*'

REVERT

and should be OK....

June 16, 2011 9:32 AM
 

Sachin said:

Hi Tibor,

I would like to thank you for your highly informative posts.

They are of immense help to a lot of us.

I have one (stupid) question.

If we create a proxy credential for a windows account as below:

EXEC sp_xp_cmdshell_proxy_account 'ADVWKS\Max04', 'ds35efg##65";

GO

Lets say, after a few months, for some reason the password changes for the account 'ADVWKS\Max04' what will be the impact?

I think that xp_cmdshell will stop execting for non sys admin users.

But any impact other than that...as in will repeated calls to xp_cmdshell lock the account or something?

June 28, 2011 12:42 AM
 

TiborKaraszi said:

Sachin,

Glad it is helpful. Yes, xalls to xp_cmdshell by non-sysadmins will no longer work, and such calls can eventually cause account lock out. I suggest you use a dedicated account for this purpose because of this.

June 28, 2011 12:39 PM
 

Archana.N said:

Hi,

  I need to load excel sheet with data from table in database. For this i used Xp_cmdshell to copy a template file for creating a new file used as the target ti be loaded. Also i used linked server and openquery commands to populate data into excel from table. The entire process i carried out in a server system. Now i need to do the same from remote system. so what all permissions would be needed to get the result same as executed in the server?

Kindly help at the earliest.

August 17, 2011 7:54 AM
 

test said:

How to check that xp_cmdshell permission is given to my dbuser before getting any error

October 19, 2011 7:58 AM
 

Manveen said:

Hi,

I am facing an issue with xp_cmdshell.

I have followed all the steps:

EXEC sp_xp_cmdshell_proxy_account null

EXEC sp_configure 'xp_cmdshell', 0

RECONFIGURE

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

GO

use master

CREATE USER dbuser FROM LOGIN dbuser

GRANT EXECUTE ON xp_cmdshell TO dbuser;

EXEC sp_xp_cmdshell_proxy_account 'domain\id','pass';

EXECUTE AS login = 'dbuser'

EXEC xp_cmdshell 'DIR D:\*.*'

REVERT

GO

I am able to execute all the statements successfully except

the last one(for executing xp_cmdshell). The error is The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

What should i do??

July 31, 2012 6:34 AM
 

John said:

Tibor,

Thank you very much. Very straight forward and easy to follow. Have not had to setup a proxy before, so you turned a daunting task into a simple do-able solution. Thanks again!

August 20, 2012 12:08 PM
 

Alan said:

I am having the same exact error that Manveen is. Has anyone come up with a solution?

December 12, 2012 12:08 PM
 

Prakash said:

Hi Tibor,

It works fine for local system, but when i tried to access network drive it fails with access is denied. where as that proxy account is Administrator on that Network server.

EXEC xp_cmdshell 'DIR \\NetworkServerName\c$'

Can you please suggest ?

February 28, 2013 6:23 AM
 

Prakash said:

Hi Tibor,

I am sorry, above problem is resolved. Really my mistake.

Thank you very much for this post.

You saved my day.

CHEERS !!!!

February 28, 2013 6:27 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement