MS SQL Server BULK INSERT can't access remote input file

I am running an MS SQL Server on my home office workstation.  I have a VPN connection to a remote network.  I am running a vbscript on the remote server that establishes a connection to my SQL Server and attempts to execute a "BULK INSERT" command.  The input file is on the remote server, but because the BULK INSERT is executed on my local SQL Server, I am getting a permissions error as the SQL Server attempts to access the file.  The SQL Server is running under the LocalHost user, which does not have access to the file.  However, there is no user on my local workstation that would be recognized by the remote computer on the remote network.  Any ideas or suggestions?
glentekAsked:
Who is Participating?
 
bamboo7431Connect With a Mentor Commented:
Here's what I wrote to the folks in my company regarding BULK INSERT:
The file has to be accessible from the SQL Server box by the SqlService account; this can be accomplished in one of the following 3 ways:
- creating a share on the file server and referring to the file by share name;
- Adding SqlService account to the Administrators group on the file server and referring to the file by full name( \\servername\e$\Data\...\filename.txt ) - obviously this wouldn't work in your situation
- Manually copying the file to the DB server and referring to it by its local path

I found that the last option is usually the easier one :)
0
 
derekkrommConnect With a Mentor Commented:
If you put the file on an FTP site on the remote server, you can create a DTS package on your local workstation to download the file from the FTP site and then run the bulk insert.

- derek
0
 
derekkrommCommented:
glen, any updates?
0
 
glentekAuthor Commented:
I ended up using the bcp command line utilility instead of "BULK INSERT" SQL command.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.