Solved

xp_cmdshell MD Make Dir Access is denied

Posted on 2011-03-07
6
1,120 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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: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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 41
SQL USE DATABASE VARIABLE 5 30
SQL Syntax Grouping Sum question 7 27
xml files 7 29
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

830 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