Access denied when trying to move files with xp_cmdshell

Question

Im trying to use some T-SQL to move some files from one directory to another. Im using xp_cmdshell to call the move command Just like this:

create table #output(line varchar(2000) null)
insert into #output exec master..xp_cmdshell 'move /y "D:\files\*.txt" "D:\oldfiles"'

But the files inst move and the #output table contains this output from the move command

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

The sql server proxy account is mapped to the local administrator If i open a command prompt at enter the move command

move /y "D:\files\*.txt" "D:\oldfiles"

The files are moved perfectly

Its all happening on a sql2005 running on a w2k3 server.

Im logged into the server as local administrator

Solution 1

Can you run a "who am I?" command, like this:

exec master..xp_cmdshell 'whoami'

and tell what this returns?

EDIT:

• By the OP's comment, the commands are run as NETWORK SERVICE. Allowing NETWORK SERVICE on the directory in question solved the problem.

• "Modify" permissions are sufficient for file changes.

• If the file is on a network share instead of a local drive, make sure that the sufficient permissions are set on the share itself as well. File permissions are checked after share permissions, so if the file allows "Modify", but the share only allows "Read", the overall permissions will still be "Read".

Solution 2

As mentioned above, it missed the permission. Another simple approach is just create the folder by xp_cmdshell if possible. If it is root directory, this cannot be applied.

exec xp_cmdshell 'mkdir d:\files'
exec xp_cmdshell 'mkdir d:\oldfiles'

It ensure the folder has proper rights.

Comments

[CruelIO]

Then one way to solve it (without the need to reconfigure the SQL Server service account) would be to simply allow NETWORK SERVICE on the directory. – Tomalak

[Rachael]

@Tomalak could you please explain your answer a bit mor in depth? What does it mean to allow "Network Service" on the directory/how to do this? Is this achieved with the above line of code you posted?

[Tomalak]

@Rachael NETWORK SERVICE is a built-in Windows user account. The author of the question runs an SQL Server under this account (that's what the above line does - it tells you which user account the server runs as). This account is very restricted and does not have write permissions on most file system directories, for security reasons. If necessary, one can modify the NTFS permissions of a certain directory to allow write access, so that - like in this case - SQL Server can copy a file there.

[Christiaan Westerbeek]

Running exec master..xp_cmdshell 'set username' could also return USERNAME=MSSQLSERVER. The user that needs to be granted permissions on the folder would be NT Service\MSSQLSERVER. Also, the permission needed for moving a file is not Write, but Modify (or both but I didn't test that)

[Tomalak]

@Christiaan Coming to think about it, it's actually more useful to run whoami, because this tells the complete username. I've updated the question accordingly.