We are using ASA 11.0.1 build 2044. The database runs as a windows service using the local system account.
I need to copy data files from our web server to our database server before a stored procedure runs. The xopy command is in a batch file and it works fine if I double-click on it or if I execute it from Start>>Run. However, I need to call it as part of a procedure using xp_cmdsheell. When I try to run it this way, nothing happens.
Here is the simple script where 'XXXX' is a parameter I am passing to the batch file:
The copy.bat has the following syntax:
Again, this is being run on the database server and we are trying to get files from the web server. The web server is mapped to the database server in windows explorer as follows: c$ on 'server1' (T:). I've modified copy.bat in different ways to get it to work without success. For example, I've tried the following in an attempt to account for UNC mapping:
What is the correct syntax for the xcopy command in this situation? Is this a rights/permissions issue on the folder I am trying to copy from? Thanks, Tom
The problem is that the local system account has no network rights by default. You can either change that, or run the service under a user with the necessary rights.
answered 06 Oct '10, 17:57
If you do not want to change the service username, you can try using the Sysinternals tool PSEXEC. That will allow you to specify a username/password to use to run the command either as you specify or to run it on the remote system (web) back to the DB system.
It all depends on what control you have over the usernames and rights on each system.
answered 06 Oct '10, 21:02
In addtion to Justin's correct answer:
A third (and in my mind better) solution is to leave the service under the local system account (and do not give that account network rights for reasons of security) but to make a temporary network connection as a specific domain user with the according rights, something like
When these connect/disconnect calls are put into separate procedures, you could even use ALTER PROCEDURE SET HIDDEN to hide any credentials.
We have used this method to copy backup files to a net share for years.
In addition, we typically use the 2> option with xp_cmdshell to pipe stderr messages to a file, something like
and in case nRetCode is <>´0 (which shows an error condition), read any OS error messages with xp_readfile('C:\MyError.txt').
answered 07 Oct '10, 07:38