Bulk Insert is not working through an Agent Job

Question

Post reply

Bulk Insert is not working through an Agent Job

sgalbra

Mr or Mrs. 500

Points: 556
More actions
October 14, 2010 at 1:15 pm

#229647

This should be a good lesson in how the bulk import works and how the Agent Jobs work in terms of security requirements.
When performing a bulk insert, it is important that the logged in user have read or write permissions on the folder and file that is being read or written. When doing this through a SQL Server Agent Job you have to aware of the job settings for "Execute as".
I have a case where I set the "Execute as" person to someone who is in the SQL Server sysadm role and who also in the Administrators group on the server. However they are not able to do the bulk import (and neither can my similarly set up account with a similar situation). Our Agent service normally runs under the local system account in our test server. This also didn't work in our production server which uses a domain account normally for running jobs. Here is the error code in the job:
Date10/14/2010 1:56:28 PM
LogJob History (<job_name>)
Step ID1
Server<server>
Job Name<job_name>
Step Nameimport
Duration00:00:00
Sql Severity16
Sql Message ID4834
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: <domain>\<user>. You do not have permission to use the bulk load statement. [SQLSTATE 42000] (Error 4834). The step failed
Can someone tell me what is missing? To replicate the problem here is some code (replace the file path):
Thanks,
Scott

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

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

sgalbra Mr or Mrs. 500 Points: 556 More actions · Answer 1

I can't seem to post the code after many tries. I wanted to make it easier for someone to replicate the problem.

sgalbra Mr or Mrs. 500 Points: 556 More actions · Answer 2

Ok this is an attempt at the abbreviated version of the code because the rest won't post (just a bulk load statement).

CREATE TABLE tempTable_test

(

column1 VARCHAR(50),

column2 VARCHAR(50),

column3 VARCHAR(50),

)

Here is the sample data file:

a,test,file

for,testing,if

bulk,import,works

Thanks,

Scott

sgalbra Mr or Mrs. 500 Points: 556 More actions · Answer 3

BULK INSERT tempTable_test

FROM '\\<server>\<folderpath>\<csv file>.csv'

WITH

(

FIRSTROW = 1,

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

sgalbra Mr or Mrs. 500 Points: 556 More actions · Answer 4

sgalbra

Mr or Mrs. 500

Points: 556

October 18, 2010 at 2:44 pm

#1238734

I guess no one else can replicate the problem?

sgalbra Mr or Mrs. 500 Points: 556 More actions · Answer 5

Thanks everyone for leaving me on my own to figure this out. 😛

The solution here is that if you want to use a BULK IMPORT function in the SQL Agent, you cannot fill in a value for the "Execute As" job step property. Therefore if you need to access an external file then you'll need a domain account I guess as the SQL Agent service account.

Also, extra windows permissions are not required. Having the sysadm role for the SQL Agent service account was sufficient.