Link to home
Create AccountLog in
Avatar of T Hoecherl
T HoecherlFlag for United States of America

asked on

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
Avatar of David Todd
David Todd
Flag of New Zealand image

Hi,

When you ran

execute sp_configure 'xp_cmdshell', 1;

to enable xp_cmdshell, did you also run

reconfigure

Regards
  David
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\'
Avatar of T Hoecherl

ASKER

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.
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).
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. :)
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
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?
Vitor Montalvao,

I did enable it on the new server.
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.
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.
Zberteoc,

Full control solved the problem.  Am I opening myself up to serious security breach by giving Full Control to the Everyone user?
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks for your help.
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