?
Solved

xp_cmdshell MD Make Dir Access is denied

Posted on 2011-03-07
6
Medium Priority
?
1,155 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:Greg Rowland
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Greg Rowland
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 200 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1800 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:Greg Rowland
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

770 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