<div>
Hi,<br />
<br />
When you ran<br />
<br />
execute sp_configure 'xp_cmdshell', 1;<br />
<br />
to enable xp_cmdshell, did you also run<br />
<br />
reconfigure<br />
<br />
Regards<br />
&nbsp; David
</div>


<div>
make sure the &quot;C:\software\test.xlsx C:\Software\MovedTest.xlsx<wbr />'&quot; <br />
is on the server <br />
or use UNC path &nbsp;e.g. \\yourPC\Share\MovedTest.x<wbr />lsx<br />
<br />
and make sure the Sql server service account has right to access this file\folder\share (NTFS\Share &nbsp;rights)
</div>


<div>
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.<br />
<br />
In order to verify that first run:<br />
<br />
EXEC master..xp_cmdshell 'dir C:\software\'
</div>


<div>
DTodd,<br />
Yes, I did reconfigure.<br />
<br />
EugeneZ,<br />
Folder is on the server, but I will try UNC pathing. &nbsp;I will also verify that SQL server service account has access to the share.<br />
<br />
Zberteoc,<br />
<br />
I will run the command and see what happens.
</div>


<div>
if &quot;C:\software\test.xlsx' on the sql server <br />
just make sure sql server service account has NTFS permissions <br />
<br />
follow above posted recommendations (.just 2 cents - maybe try use not C:drive if you can).
</div>


<div>
Other 2 cents here.<br />
<br />

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


<div>
&quot;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).&quot;<br />
<br />
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. :)
</div>


<div>
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.<br />
link: <a href="https://technet.microsoft.com/en-us/library/cc771070.aspx" rel="ugc">https://technet.microsoft.com/en-us/library/cc771070.aspx</a><br />
This might give you the puzzle-pice you need to unravel this problem you're facing.<br />
<br />
Regards Marten
</div>


<div>
Zberteoc,<br />
<br />
This the result of running EXEC master..xp_cmdshell 'dir C:\software\' <br />
<br />
output<br />
&nbsp;Volume in drive C has no label.<br />
&nbsp;Volume Serial Number is CC35-8484<br />
NULL<br />
&nbsp;Directory of C:\software<br />
NULL<br />
02/04/2016 &nbsp;01:23 PM &nbsp; &nbsp;&lt;DIR&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.<br />
02/04/2016 &nbsp;01:23 PM &nbsp; &nbsp;&lt;DIR&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;..<br />
02/04/2016 &nbsp;10:12 AM &nbsp; &nbsp; &nbsp; &nbsp;26,481,656 AccessDatabaseEngine.exe<br />
02/04/2016 &nbsp;10:00 AM &nbsp; &nbsp; &nbsp; &nbsp;28,631,968 AccessDatabaseEngine_x64.e<wbr />xe<br />
02/04/2016 &nbsp;10:57 AM &nbsp; &nbsp;&lt;DIR&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ERS_ACON<br />
02/04/2016 &nbsp;10:57 AM &nbsp; &nbsp;&lt;DIR&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ERS_ARC<br />
02/04/2016 &nbsp;01:05 PM &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;99,831 Test.xlsx<br />
02/04/2016 &nbsp;01:23 PM &nbsp; &nbsp;&lt;DIR&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TestFolder<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 File(s) &nbsp; &nbsp; 55,213,455 bytes<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 Dir(s) &nbsp;21,605,122,048 bytes free<br />
NULL<br />
<br />
I believe that indicates that the folder and file exist locally on the SQL Server machine where I am running the code, right?
</div>


<div>
Vitor Montalvao,<br />
<br />
I did enable it on the new server.
</div>


<div>
Hi,<br />
<br />
The folder exists on the server, and can see the text.xlsx file. <br />
<br />
But that doesn't mean that the SQL Server process has rights to move the file.<br />
<br />
Regards<br />
&nbsp; David
</div>


<div>
Correct, that confirms 3 things:<br />
<br />
1. xp_cmdshell is enabled on the server<br />
2. the folder and file exist on the SQL server where the xp_cmdshell was executed<br />
3. the sql account doesn't have WRITE access to that folder<br />
<br />
The solution is to remotely log in to that box and go to that folder and in its properties window under the &nbsp;security tab either add the SQL account or just add Everyone (the general user) to the users list and give write or full control.<br />
<br />
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:<br />

<pre><code class="code-1 nolinks" id="code-20-41456886-1"><span>declare @cmdstring varchar(1000)</span>
<span>create table #output(id int identity, line varchar(1000))</span>
<span>set @cmdstring = 'move C:\software\test.xlsx C:\Software\MovedTest.xlsx'</span>
<span>insert into #output (line) exec master..xp_cmdshell @cmdstring</span>
<span>select * from #output where line is not null order by id</span>
</code></pre>
</div>


<div>
To identify the SQL server services accounts run this:<br />
<br />
select * from sys.dm_server_services<br />
<br />
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.
</div>


<div>
Zberteoc,<br />
<br />
Here is the code I am now using:<br />
<br />
declare @cmdstring varchar(1000)<br />
create table #output(id int identity, line varchar(1000))<br />
set @cmdstring = 'move C:\software\test.xlsx C:\Software\MovedTest.xlsx<wbr />'<br />
insert into #output (line)<br />
exec master..xp_cmdshell @cmdstring<br />
select * from #output where line is not null order by id<br />
<br />
Here is the output from the select statement:<br />
<br />
id &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;line<br />
1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Access is denied.<br />
2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 0 file(s) moved.<br />
<br />
I have given write permission to the Everyone user on the folder
</div>


<div>
Make sure you do that to C:\software folder.
</div>


<div>
Zberteoc,<br />
<br />
Full control solved the problem. &nbsp;Am I opening myself up to serious security breach by giving Full Control to the Everyone user?
</div>


<div>
thoecherl: you could &nbsp;get your problem resolved 3 days ago using my post solution<br />
<br />
&nbsp;&quot;just make sure sql server service account has NTFS permissions &quot;&nbsp;<br />
<br />
just in case<br />
<br />
NTFS Permissions<br />
<a href="http://www.ntfs.com/ntfs-permissions.htm" rel="ugc">http://www.ntfs.com/ntfs-permissions.htm</a>
</div>


<div>
<div class="content wysiwyg-content">
A stored procedure includes the following code:<br />
<br />
declare @cmdstring varchar(1000)<br />
set @cmdstring = 'move C:\software\test.xlsx C:\Software\MovedTest.xlsx<wbr />'<br />
exec master..xp_cmdshell @cmdstring<br />
<br />
This has worked for a few years, but everything was recently moved to a new SQL Server. &nbsp;Now when I try to run this code I get this result:<br />
<br />
output<br />
Access is denied.<br />
&nbsp; &nbsp; &nbsp; &nbsp; 0 file(s) moved.<br />
NULL<br />
<br />
I have done the following:<br />
<br />
1. &nbsp;Make sure I am logged in to SSMS as an administrator.<br />
2. &nbsp;Enable xp_cmdshell on the server<br />
3. &nbsp;Create a proxy account and credential<br />
4. &nbsp;Grant security to xp_cmdshell for my user and for the proxy user<br />
5. &nbsp;Ensure that my user and the proxy user both have write permissions to the folders involved in the move process<br />
<br />
What else must I do to be able to use xp_cmdshell?<br />
<br />
T
</div>
</div>


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

xp_cmdshell - Access is Denied

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.