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.

Answer 1

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.

Answer 2

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

Answer 3

No.  It is the Windows account that the SQL Server service is running under.  Specifically, you can find this within SQL Server Configuration Manager.

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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.

Answer 10

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.

Answer 11

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

Answer 12

Thank you Michael. Your answer helped solve my problem, instead of the blah blah blah of other users