BCP/BULK INSERT User Logon

Question

BCP/BULK INSERT User Logon

wbeetge

SSChasing Mays

Points: 636
More actions
April 13, 2004 at 7:28 pm

#108122

Can anyone tell me what I need to make the following work.
I have a working version of a Bulk Insert with a format file. It works as long as I run it on my local machine, and local SQL Server.
The user I use to connect is a SQL user with Bulk Insert access.
But when I move the Database to another server, The same SQL user exists on the remote SQL server, and it can execute the BCP.exe. My problem is that the datafile and format file lie on another server where the users have access to save their csv files. Which user is used to determine the access to the network for the datafile ?
-and-
What will happen if I move the execution to .Net on the intranet (again a different Server)?
Would the ASPNET user also need have any special attention (and where) - would it actually be used, and if so, would it need to exist on the SQL server as a valid NT User ?
Do I need to only use a valid NT User on the SQL Server and not SQL Users ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jaredsimi SSCrazy Points: 2420 More actions · Answer 1

In order to perform bulk insert, the sql server service must have access to the network share. If you are running as LocalSystem (the default), that will not have. Change the service to run as a user with network access, and the bulk insert will work across the network. A caution, however, working doesn't necessarily mean fast.

wbeetge SSChasing Mays Points: 636 More actions · Answer 2

Hi Jaredsimi

Thank you for your reply.

Please explain Change the service to run as a user with network access for me.

How do I get the service to run as a user ?

wbeetge SSChasing Mays Points: 636 More actions · Answer 3

Ok, I see you can do this through Enterprie Manager.

But, on a huge production server, Would this not be a huge impact ?

The SQL Server we are using is hosting JDE. How can we safely create a user for the service that will not cause any access problems for certain connections - and what should the criteria be for creating this user ?

jaredsimi SSCrazy Points: 2420 More actions · Answer 4

its doesn't affect access to the server. the sql server process itself is just running under a different account. I would recommend that the account be an administrator on the sql server box but its quite possible it could run with guest privileges only. The following article describes the permissions of the LocalSystem account

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc/base/localsystem_account.asp

Good Luck.

wbeetge SSChasing Mays Points: 636 More actions · Answer 5

Thank you very much. I see a light shining at the end of the tunnel

Cheers