xp_cmdshell - Access is Denied

T Hoecherl
T Hoecherl used Ask the Experts™
on
A stored procedure includes the following code:

declare @cmdstring varchar(1000)
set @cmdstring = 'move C:\software\test.xlsx C:\Software\MovedTest.xlsx'
exec master..xp_cmdshell @cmdstring

This has worked for a few years, but everything was recently moved to a new SQL Server.  Now when I try to run this code I get this result:

output
Access is denied.
        0 file(s) moved.
NULL

I have done the following:

1.  Make sure I am logged in to SSMS as an administrator.
2.  Enable xp_cmdshell on the server
3.  Create a proxy account and credential
4.  Grant security to xp_cmdshell for my user and for the proxy user
5.  Ensure that my user and the proxy user both have write permissions to the folders involved in the move process

What else must I do to be able to use xp_cmdshell?

T
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David ToddSenior Database Administrator

Commented:
Hi,

When you ran

execute sp_configure 'xp_cmdshell', 1;

to enable xp_cmdshell, did you also run

reconfigure

Regards
  David

Commented:
make sure the "C:\software\test.xlsx C:\Software\MovedTest.xlsx'"
is on the server
or use UNC path  e.g. \\yourPC\Share\MovedTest.xlsx

and make sure the Sql server service account has right to access this file\folder\share (NTFS\Share  rights)
When you execute xp_cmdshell command it will run against the SQL Server host machine were you run it. So the folder and file you refer to, C:\software\test.xlsx, have to exist locally on the SQL server machine in order to work.

In order to verify that first run:

EXEC master..xp_cmdshell 'dir C:\software\'
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

T HoecherlDeveloper

Author

Commented:
DTodd,
Yes, I did reconfigure.

EugeneZ,
Folder is on the server, but I will try UNC pathing.  I will also verify that SQL server service account has access to the share.

Zberteoc,

I will run the command and see what happens.

Commented:
if "C:\software\test.xlsx' on the sql server
just make sure sql server service account has NTFS permissions

follow above posted recommendations (.just 2 cents - maybe try use not C:drive if you can).
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Other 2 cents here.

This has worked for a few years, but everything was recently moved to a new SQL Server.
That's because someone forgot to enable it in the new server. xp_cmdshell is disabled by default for security reasons (it gives access to the operating system and can be used to run malicious code).
"That's because someone forgot to enable it in the new server. xp_cmdshell is disabled by default for security reasons (it gives access to the operating system and can be used to run malicious code)."

That is somewhat a dispute among DBAs and system admins. The only ones able to use xp_cmdshell command are the sysadmins, which usually HAVE all the right on the machine too. If you don't trust your DBAs then your're in trouble. :)
Marten RuneSQL Expert/Infrastructure Architect

Commented:
In a server you can enable file audit. If you do so, you'll see what is trying to do what and why it's denied.
link: https://technet.microsoft.com/en-us/library/cc771070.aspx
This might give you the puzzle-pice you need to unravel this problem you're facing.

Regards Marten
T HoecherlDeveloper

Author

Commented:
Zberteoc,

This the result of running EXEC master..xp_cmdshell 'dir C:\software\'

output
 Volume in drive C has no label.
 Volume Serial Number is CC35-8484
NULL
 Directory of C:\software
NULL
02/04/2016  01:23 PM    <DIR>          .
02/04/2016  01:23 PM    <DIR>          ..
02/04/2016  10:12 AM        26,481,656 AccessDatabaseEngine.exe
02/04/2016  10:00 AM        28,631,968 AccessDatabaseEngine_x64.exe
02/04/2016  10:57 AM    <DIR>          ERS_ACON
02/04/2016  10:57 AM    <DIR>          ERS_ARC
02/04/2016  01:05 PM            99,831 Test.xlsx
02/04/2016  01:23 PM    <DIR>          TestFolder
               3 File(s)     55,213,455 bytes
               5 Dir(s)  21,605,122,048 bytes free
NULL

I believe that indicates that the folder and file exist locally on the SQL Server machine where I am running the code, right?
T HoecherlDeveloper

Author

Commented:
Vitor Montalvao,

I did enable it on the new server.
David ToddSenior Database Administrator

Commented:
Hi,

The folder exists on the server, and can see the text.xlsx file.

But that doesn't mean that the SQL Server process has rights to move the file.

Regards
  David
Correct, that confirms 3 things:

1. xp_cmdshell is enabled on the server
2. the folder and file exist on the SQL server where the xp_cmdshell was executed
3. the sql account doesn't have WRITE access to that folder

The solution is to remotely log in to that box and go to that folder and in its properties window under the  security tab either add the SQL account or just add Everyone (the general user) to the users list and give write or full control.

I also recommend you to add something to that code in order to be able to check the output in an automatic fashion if you need. For this you will create a temp table called #output with 2 columns, id as integer identity and line as varchar. You will insert into this file the output from the EXEC statement and then you can have control on the actual output based on which you can make later decisions:
declare @cmdstring varchar(1000)
create table #output(id int identity, line varchar(1000))
set @cmdstring = 'move C:\software\test.xlsx C:\Software\MovedTest.xlsx'
insert into #output (line) exec master..xp_cmdshell @cmdstring
select * from #output where line is not null order by id

Open in new window

To identify the SQL server services accounts run this:

select * from sys.dm_server_services

The information is in the service_account column. Copy that from the row with SQL Server (...) as servicename and add it in the security tab of that folder with write r full control.
T HoecherlDeveloper

Author

Commented:
Zberteoc,

Here is the code I am now using:

declare @cmdstring varchar(1000)
create table #output(id int identity, line varchar(1000))
set @cmdstring = 'move C:\software\test.xlsx C:\Software\MovedTest.xlsx'
insert into #output (line)
exec master..xp_cmdshell @cmdstring
select * from #output where line is not null order by id

Here is the output from the select statement:

id      line
1      Access is denied.
2              0 file(s) moved.

I have given write permission to the Everyone user on the folder
Give it full control.
Make sure you do that to C:\software folder.
T HoecherlDeveloper

Author

Commented:
Zberteoc,

Full control solved the problem.  Am I opening myself up to serious security breach by giving Full Control to the Everyone user?
Well, anyone that accesses that folder can wipe it out. :o) You can remove Everyone and replace it with the service_account I mentioned above.
T HoecherlDeveloper

Author

Commented:
Thanks for your help.

Commented:
thoecherl: you could  get your problem resolved 3 days ago using my post solution

 "just make sure sql server service account has NTFS permissions " 

just in case

NTFS Permissions
http://www.ntfs.com/ntfs-permissions.htm

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial