Solved

xp_cmdshell MD Make Dir Access is denied

Posted on 2011-03-07
6
1,107 Views
Last Modified: 2012-05-11
Trying to create a new sub fold on another computer on the same domain.
Created a Domain User Account xpcmd. Granted full control to the target drive and folder to Domain User Account xpcmd.

EXECUTE AS LOGIN = 'domain\xpcmd'
EXEC master..xp_cmdshell 'MD "\\NetworkComputer\c$\Temp\Test"'
GO
REVERT  
GO

This has been tried on two different domains and networks. One being a Windows 7 Box as a target, the other being another Windows 2003 SQL 2005 Server  as the target.

In every case still returns “Access is denied.”

0
Comment
Question by:SurferJoe
  • 2
  • 2
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35065636
Have you enabled xp_cmdshell ?
Is the startup account for the SQL Server service a domain account or Local System account?
0
 
LVL 4

Author Comment

by:SurferJoe
ID: 35065776
>>Have you enabled xp_cmdshell ?
Yes.

>>Is the startup account for the SQL Server
Local System account
0
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 50 total points
ID: 35066556
It seems that Local System Account does not have permission to create directories.
If login who is executing xp_cmdshell sp (in your case domain\xpcmd) belongs to sysadmin server role then SQL Server will use service account. But for any non sysadmin account you need to use which Windows account to use. To do that you need to use another sp which is sp_xp_cmdshell_proxy_account.

--this is proxy for all nonsysadmins
EXEC sp_xp_cmdshell_proxy_account 'domain\account','pwd'
EXECUTE AS LOGIN = 'domain\xpcmd'
EXEC master..xp_cmdshell 'MD "\\NetworkComputer\c$\Temp\Test"'
REVERT

--Cleanup
EXEC sp_xp_cmdshell_proxy_account null

Please bear in mind that if login 'domain\xpcmd' is in sysadmin role SQL Server is gonna use it's service account.

Take care,
Daniel
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 450 total points
ID: 35068958
>>Local System account <<
That explains your problem.  A local system account does not have permissions to other servers, you need to change it to a domain account that has the appropriate access.
0
 
LVL 4

Author Closing Comment

by:SurferJoe
ID: 35070807
Daniel_PL, suggestion is consistent with Microsoft Documentation and one would expect it to work.

However acperkins final comment is the silver bullet.
Creating a new SQLAdmins Domain user account with the required privileges.
Changing the SQL Server Service to run under that account mitigates the problem.
This did require a Server Reboot to fully take effect.

Somehow I  knew this was going to be the answer, just don’t like making changes to a production server.
Thanx,

Folk’s,

Greg
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35071577
Sure. Maybe I didn't wrote this clearly enough. I tried to show you how SQL Server works, besides creating dedicated domain account is really just another good practise which you can find in MS documentations which you've just mentioned. My answer didn't require reboot so you could use it in working enviroment.
In production there are many different cases, so by me, it's good to know a little more just as I tried to say about the case.

Take care,
Daniel
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question