none
cannot execute xp_cmdshell. Access is denied while copying data over the network

    Question

  • Hi,

    I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).
    Here is the T-SQL code that I am trying to execute:

    EXEC xp_cmdshell 'copy D:\Data\file.txt  \\MachineB\Documents'

    Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.

    I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:
    "Check in Services and make sure that the MSSQLServer service is run as a domain user and that domain user has rights to these network resources."

    Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?
    Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

    Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.
    Should I create a user called 'User1' on my MachineB(Destination Machine)?

    I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

    Thank you very much once again.




    Monday, August 07, 2006 7:43 PM

Answers

  • Local system is the local machine account.  That would be why you are getting an access denied on a network resource.  The SID for localsystem is not valid external to the machine.  In order to be able to execute that command you would need to create a Windows login and then use that login to run the SQL Server services.  That login would have to have permissions on the network resource you are trying to write to.
    Monday, August 07, 2006 10:32 PM
    Moderator
  • No.  It is the Windows account that the SQL Server service is running under.  Specifically, you can find this within SQL Server Configuration Manager.
    Monday, August 07, 2006 8:19 PM
    Moderator
  • No, in fact, there are entire feature sets that you can't use in SQL Server if you are running under the localsystem account, replication being one of them.  I don't run any SQL Server under a local machine account.  I always run them under specific user accounts and in particular regular domain user accounts.
    Tuesday, August 08, 2006 2:32 PM
    Moderator

All replies

  • Open the Surface Area Configuration Manager.  Go to the features section.  Make sure that xp_cmdshell is enabled.  (It is turned off by default.)  Then once you have enabled xp_cmdshell, make sure that the SQL Server service account has the appropriate permissions granted.
    Monday, August 07, 2006 7:52 PM
    Moderator
  • Hi Michael,

    Thank you very much for your reply. xp_cmdshell is already enabled. How do I check the permissions of SQL Server Srvice account? Is that the same account which I use to connect to SQL SERVER through Management Studio?

    Thanks

    Monday, August 07, 2006 8:14 PM
  • No.  It is the Windows account that the SQL Server service is running under.  Specifically, you can find this within SQL Server Configuration Manager.
    Monday, August 07, 2006 8:19 PM
    Moderator
  •  Michael Hotek wrote:
    No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.


    Ok I went to the SQL SERVER Configuration Manager. Then I clicked on SQL SERVER 2005 Services in the left pane. On the right pane, it listen 5-6 services running like Intergration Service, Analysis Service, Reporting Service, and so on. One of them was SQL SERVER(MSSQLSERVER), so I think this must be the one that you were talking about. I right clicked on it-> Properties. Under the properties tab, I saw Log on as: Built-in Account (radio button) was checked and in the dropdown below it, Local System was selected. So basically the SQL Server Service is running under Local System. Now how do I figure out what user is associated with Local System?

    Thank you once again for the help.Much appreciated.


    Monday, August 07, 2006 10:21 PM
  • Local system is the local machine account.  That would be why you are getting an access denied on a network resource.  The SID for localsystem is not valid external to the machine.  In order to be able to execute that command you would need to create a Windows login and then use that login to run the SQL Server services.  That login would have to have permissions on the network resource you are trying to write to.
    Monday, August 07, 2006 10:32 PM
    Moderator
  • Thanks a lot Michael, that really makes sense .

    Now for the extension based on your reply. Are there any disadvantages of using a Windows user to run the SQL Server services instead of a Local Machine account?

    Thank you very very much for your prompt reply and insightful suggestion.


    Monday, August 07, 2006 10:42 PM
  • No, in fact, there are entire feature sets that you can't use in SQL Server if you are running under the localsystem account, replication being one of them.  I don't run any SQL Server under a local machine account.  I always run them under specific user accounts and in particular regular domain user accounts.
    Tuesday, August 08, 2006 2:32 PM
    Moderator
  • I just did this and it worked like a charm. You someone already posted the answer. Go to SQL Server Configuration Manager. Right click on Sql Server, click properties and change the user account to run under a domain or local pc account. that has access to the share.
    Friday, July 27, 2007 5:02 AM
  • Just an obs, if you are running the command under a JOB, just need to change the user at the SQL Server AGENT, what may be much easier than changing on MSSQLServer service.
    Wednesday, April 23, 2008 2:56 PM
  • Here is a way you can access network shares;

    EXEC

     

    master..xp_cmdshell "net use t: \\<server>\<share> <password> /user:<username> /persistent:yes"
    EXEC master..xp_cmdshell 'copy D:\Data\file.txt  \\MachineB\Documents'
    EXEC master..xp_cmdshell "net use t: /delete"

    I had a simular issue and this is how I resolved it.

    • Proposed as answer by zino Friday, April 06, 2012 8:33 PM
    Friday, July 02, 2010 9:30 PM
  • YES, Finally. I have been trying to get this to work in various projects. I run SQL Server services under a domain account, but the copy always failed anyway. I set up shares and granted r/w to everyone, to no avail. The same copiy commands that worked in a cmd window failed using xp_cmdshell. I still don't understand why this was neseccary, but this fixed it.  If anyone wants to explain, I'm all ears and thank you.

    Thank you Michael!


    Rod
    Saturday, September 24, 2011 3:34 PM
  • Thank you Michael. Your answer helped solve my problem, instead of the blah blah blah of other users
    Friday, April 06, 2012 8:33 PM
  • Thank you Michael You save my life...
    Friday, September 06, 2013 12:45 PM
  • Thanks Michael, your commands worked a treat - exactly what we needed!

    Cheers!

    Monday, September 16, 2013 11:44 PM
  • Worked for me, The SQL JOB was executing under NETWORK SERVICES user, but even with the permissions on the folder the access was denied.

    Tried that using a user with access to the folder and it worked! Thanks!

    Wednesday, May 27, 2015 4:04 PM