Bulk Loading: TempFilePath security question

I've got a program written that runs on one computer and performs bulk
updates to a SQL Server 2000 table on a different computer. I also
need transactional behavior, so I have to specify a value for
TempFilePath on my SQLXMLBulkLoad3Class object.
I do not have permissions to create folders, files, or interact in any
way with the computer hosting SQL server. This means that the value of
TempFilePath must be a UNC path that can be written to by the
application performing bulk loads, and (at a minimum) read from by the
computer hosting SQL server.
I'd like to make access to this UNC path as restricted as possible. I
am using SQL authentication, not Windows authentication.
How can I tell what ID will try from the SQL Server computer to read
from the TempFilePath?
How much permission will this ID need in the directory? Read only?
Read/write?If you do this with a connection using standard SQL login, then the thread
that is running the query will be using the account that the SQL Server
service runs under to access the file share.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
<isbat1@.yahoo.com> wrote in message
news:1128017182.904592.4100@.z14g2000cwz.googlegroups.com...
> I've got a program written that runs on one computer and performs bulk
> updates to a SQL Server 2000 table on a different computer. I also
> need transactional behavior, so I have to specify a value for
> TempFilePath on my SQLXMLBulkLoad3Class object.
> I do not have permissions to create folders, files, or interact in any
> way with the computer hosting SQL server. This means that the value of
> TempFilePath must be a UNC path that can be written to by the
> application performing bulk loads, and (at a minimum) read from by the
> computer hosting SQL server.
> I'd like to make access to this UNC path as restricted as possible. I
> am using SQL authentication, not Windows authentication.
> How can I tell what ID will try from the SQL Server computer to read
> from the TempFilePath?
> How much permission will this ID need in the directory? Read only?
> Read/write?
>

Bulk insert problem

Hey,

I am trying to do a bulk insert from a txt file. I am trying to allow the query to read the txt file off the users local computer. This is the code I am using:

BULK INSERT TbleTestBulk
FROM 'C:\westportela.dat'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

I also tried this

BULK INSERT TbleTestBulk
FROM '\\host_name\C$\westportela.dat'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

and this

BULK INSERT macomber
FROM '\\wcsserver\SSP\txt frm mms\WES.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

none work.

I basically get this error for each:
Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file '\\wcsserver\SSP\txt frm mms\WES.txt' could not be opened. Operating system error code 53(The network path was not found.).

Anyone got any suggestions. I am really new to this so all help would be welcomed.

Thanks

MikeDid the client share that location?

Why not copy the file to the server...I would suggest that that is the preferred method...you would want to take the network out of the equation when loading or dumping data...

My own opinion (MOO) *

* Actually not really...it's in a few books I've read

bulk insert on remote computer

Hi there!
I am using bulk insert to enter data in tables. I try to bulk insert
data on a remote server but the bulk insert statement must access
files located on localhost computer (from where I query a bulk insert
with query analyser)..and I get a 'access denied' from sqlserver
the only way I found to prevent this, is to log on the remote computer
as the same user that the local computer, to give permissions...
is there any way to do this'
thanks a lot :)
++You need to put the file in some location accessible from the server.
If that location is on the network then make sure you specify the UNC
path name rather than use drive letters.
David Portas
SQL Server MVP
--|||On 26 Apr 2005 07:28:18 -0700, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
yes, I use UNC path..but I guess this problem is due to a windows
configuration..I mean I must define the same user on both computers to
allow one to access to other whatever programs are used...
++
Vince

>You need to put the file in some location accessible from the server.
>If that location is on the network then make sure you specify the UNC
>path name rather than use drive letters.|||Just give the access rights to whatever account you use for the SQL
Server service. If you don't know what account that is then check the
Log On properties of MSSQLSERVER in the Services dialog under Control
Panel.
David Portas
SQL Server MVP
--