Solved

xp_cmdshell MD Make Dir Access is denied

Posted on 2011-03-07
6
1,093 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now