BULK INSERT AND FILE ACCESS DENIED ISSUE

• mymail.default

  SSCommitted

  Points: 1752

  More actions

  February 17, 2011 at 10:24 am

  #234559

  Hello everyone,

  Little stumped with this problem trying to do a bulk insert into a table and it was working fine for a while and suddenly started giving this:

  Cannot bulk load because the file 'file.txt' could not be opened. Operating system error code 5(Access is denied.)

  I checked the SQL Service account to have permissions to this file.

  With some testings, I found that if I run the bulk stmt. as a "sa" user, it works ok, however, when I run it as a windows domain user(which has all the permissions on the file), it fails with the error.

  Any ideas why it this is so?

  Thanks

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  February 17, 2011 at 11:06 am

  #1288408

  See the section on Permissions in the BOL article for BULK INSERT:

  http://msdn.microsoft.com/en-us/library/ms188365.aspx

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Tien Tran

  SSC Veteran

  Points: 222

  More actions

  December 20, 2012 at 8:42 pm

  #1570700

  in addition to setting up delegation, I disabled the named pipe protocol on SQL server and it works perfectly

• Mohammed Imran Ali

  SSCrazy

  Points: 2125

  More actions

  December 20, 2012 at 8:48 pm

  #1570702

  mymail.default (2/17/2011)

  ---

  I checked the SQL Service account to have permissions to this file.

  Check if the SQL Server Agent service account has access to this file (and location).