xp_cmdshell - Access is Denied

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
T HoecherlDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:
Hi,

When you ran

execute sp_configure 'xp_cmdshell', 1;

to enable xp_cmdshell, did you also run

reconfigure

Regards
  David
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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)
ZberteocCommented:
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\'
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

T HoecherlDeveloperAuthor 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.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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ãoMSSQL Senior EngineerCommented:
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).
ZberteocCommented:
"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 ArchitectCommented:
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 HoecherlDeveloperAuthor 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 HoecherlDeveloperAuthor Commented:
Vitor Montalvao,

I did enable it on the new server.
David ToddSenior Database AdministratorCommented:
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
ZberteocCommented:
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

ZberteocCommented:
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 HoecherlDeveloperAuthor 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
ZberteocCommented:
Give it full control.
ZberteocCommented:
Make sure you do that to C:\software folder.
T HoecherlDeveloperAuthor Commented:
Zberteoc,

Full control solved the problem.  Am I opening myself up to serious security breach by giving Full Control to the Everyone user?
ZberteocCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
T HoecherlDeveloperAuthor Commented:
Thanks for your help.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.