Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

xp_cmdshell MD Make Dir Access is denied

Posted on 2011-03-07
6
Medium Priority
?
1,195 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
  • 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 15

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 15

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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

564 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