How do I move files from one server to another using SQL JOB

I've written a VB Script to move files from a folder on one server to a folder on another.  If I run the script in the operating system, it runs fine but if I run it vai a SQL Job, it reports success but doesn't do the move.
Looking in the Job History, it tells me that I have a permissions problem.  I assume this is because the Local System Account that is running SQL Agent doesn't have permission on the remote server.
So, I have three quesitons:
1. How do I give the Local System Account on the server running SQL Agent permission to write files on the remote server?
2. Some posts that I've looked at suggest that if I create a .bat to call the CScript and then call the .bat from the SQL JOB, it will work.  If this is true, why would a .bat have permissions?
3. All the posts I've seen are very old.  Is there a newer better way of doing this?
iblyidAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
radcaesarConnect With a Mentor Commented:
You need to use a proxy account with suitable permissions.

http://msdn.microsoft.com/en-us/library/ms189064%28v=SQL.90%29.aspx
0
 
iblyidAuthor Commented:
Hi radcaesar, that sounds like a plan.  I'll try it out tomorrow and let you know.  I was hoping for a more elegant solution though.  I'm running this as part of a SQL Job as the preceding step is a stored procedure that creates the files to be moved using bcp.. I'm only doing it this way as I can't get bcp to write to the remote server.  Perhaps I should experiment with using a proxy account for BCP and forget he VB altogether or even opening the file system object to do the write.
0
All Courses

From novice to tech pro — start learning today.