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

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.

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

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

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.

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

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

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

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.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.|||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|||No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.|||

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

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

Hi,

I am havingsome trouble copying data over my workgroup network from my Windows2003 Server Machine (machineA with SQL SERVER 2005) to one of mynetwork 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'

WheneverI 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 CommandPrompt (cmd.exe) with the copy command, the file copies fine over thenetwork.

I have already searched over the internet and I foundout that loads of people have the same issue, and they were suggestedsomething like this:
"Check in Services and make sure that the MSSQLServer service is run asa domain user and that domain user has rights to these networkresources."

Wellit sounds plausible, but I don't know what are the exact steps to dothis. How do I know which user is running the MSSQL Server service? Arethey referring to the user which I use to connect to my SQL ServerDatabase 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.

Hereare some details of the user that I use to login. I generally logininto my Windows 2003 Server machine with user called 'User1' and I usethe same 'User1' to connect to SQL Server through the Management StudioScreen.
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.Check out the SQL Server Surface Area Configuration under SQL Server 2005 configuration. xp_cmdshell is disabled by default. You need to enable it manually and give permissions.|||

ndinakar:

Check out the SQL Server Surface Area Configuration under SQL Server 2005 configuration. xp_cmdshell is disabled by default. You need to enable it manually and give permissions.

Thank you very much for your reply. xp_cmdshell is already enabled.

|||

modest:

"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.

You're on the rigth way goto Start->All Programs->Microsoft SQL Server2005-> Configuration Tools->SQL Server Configuration Manager->SQL Server 2005 Services->locate the service for the SQL2005 instance, should be "SQL Server (InstanceName)"->double click it-> on the Log On tab, you can see the start up account for the service. Just make sure this account has proper permission on the remote resource.

|||Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.|||

modest:

Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.

xp_cmdshell runs with SQL Server Agent permissions Microsoft have finally documented the permission requirements. Try the link below and follow the instructions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175046.aspx

|||

Caddre:

modest:

Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.

xp_cmdshell runs with SQL Server Agent permissions Microsoft have finally documented the permission requirements. Try the link below and follow the instructions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175046.aspx

Well I already have the proxy credential created for the User1 Windows account as I use that credential to execute the dtsRun command from the xp_cmdshell and that runs fine. The problem is when I am trying to copy something over the network. For example I can copy a file within my local machine by using the xp_cmdshell stored procedure, but it won;t work if the destination drive is a network drive.|||That is because you installed SQL Server with the local systems account when you do that and most developers do, SQL Server service and SQL Server Agent are without access to network resources. Try running replication service and you will see what I am talking about. Run a search for local systems account limitations in SQL Server BOL (books online). Hope this helps.|||

Caddre:

That is because you installed SQL Server with the local systems account when you do that and most developers do, SQL Server service and SQL Server Agent are without access to network resources. Try running replication service and you will see what I am talking about. Run a search for local systems account limitations in SQL Server BOL (books online). Hope this helps.

Thanks. Actually I was worried, if changing the startup user for SQLSERVER 2005 will affect my ASP/ASP.NET scripts or for that matter IIS6.0. Will this change be transparent to all the users on the machineand the other machine on my network which connect to the SQL SERVER onmy machine?|||It is not complicated just run full backup of all your databases and rerun setup and make sure you install SQL Server service and SQL Server Agent with two Domain accounts with network permissions. And no it will not affect your application if you do the above because if you run into permissions problem your backup will help if recreate the databases before the changes. But I don't think it will affect anything because you are just giving SQL Server service and the Agent network access. Hope this helps.|||

Caddre:

It is not complicated just run full backup of all your databases and rerun setup and make sure you install SQL Server service and SQL Server Agent with two Domain accounts with network permissions. And no it will not affect your application if you do the above because if you run into permissions problem your backup will help if recreate the databases before the changes. But I don't think it will affect anything because you are just giving SQL Server service and the Agent network access. Hope this helps.

Thanks Caddre very much. I just have one question based on your suggestion. What do you mean by domain accout? Do you mean like actual Domain Network on which my computer is connected? As I said in my first post, we have a workgroup network and not domain network. I know I am confusing with the word Domain. Does domain account mean, a new Windows user that I create on my machine? Or does it mean something lese?

Thank you once again.|||OK my bad create service accounts in your workgroup to run SQL Server service and SQL Server Agent and no you cannot run both on one account because the Agent can do a lot of automation in your application for you so it is good practice to run them on separate accounts. Hope this helps.

Cannot enable xp_cmdshell using DMO and VBScript

Hi,

I am trying to enable xp_cmdshell using VBScript and DMO in SQL Server 2005 Express.
But I get errror "Item cannot be found in collection corrosponding to the requested name or ordinal"

Following is the script:

Dim excmd: Set excmd = CreateObject("ADODB.Command")
Set excmd.ActiveConnection = connx ' This is connected to master database using sa account

excmd.CommandType = 4
excmd.CommandText = "sys.sp_configure"
excmd.Parameters.Refresh
excmd.Parameters(1).Value = "show advanced options"
excmd.Parameters(2).Value = 1
excmd.Parameters(3).Value = Null
excmd.Execute () ' This line fails
If (Err.Number <> 0) Then
LogText "Unable to execute stored procedure. " & Err.Number & " " & Err.Description
Exit Sub
End If

excmd.CommandText = "RECONFIGURE WITH OVERRIDE"
excmd.Parameters.Refresh
excmd.Execute ()
If (Err.Number <> 0) Then
LogText "Unable to Refresh. " & Err.Number & " " & Err.Description
Exit Sub
End If

excmd.CommandText = "sys.sp_configure"
excmd.Parameters.Refresh
excmd.Parameters(1).Value = "xp_cmdshell"
excmd.Parameters(2).Value = 1
excmd.Parameters(3).Value = Null
excmd.Execute ()

If (Err.Number <> 0) Then
LogText "Unable to execute sp xp_cmdshell. " & Err.Number & " " & Err.Description
Exit Sub
End If

excmd.CommandText = "RECONFIGURE"
excmd.Parameters.Refresh
excmd.Execute ()
If (Err.Number <> 0) Then
LogText "Unable to Refresh after running sp. " & Err.Number & " " & Err.Description
Exit Sub
End If

Could any body tell me what I am missing here.

Thanks in advance
Amit

Please do not multipost, answered in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1989334&SiteID=1

Jens K. Suessmeyer

http://www.sqlserver2005.de