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

    Question

  • I am facing a issue with bulk upload on Test Server.

    Issue: When running Openrowset command from SQL server other that Test Server query runs fine when trying to run the same command from Test Server it gives error.

    Msg 4861, Level 16, State 1, Line 1

    Cannot bulk load because the file "\\ServerName\input\FileName.csv" could not be opened. Operating system error code 5(Access is denied.).

    For example: If the command is run from System A connecting to SQL Server instance on Test Server Test Server it gives this error. If the same command with same rights is run from any other SQL server instance say Dev1 its running fine.

    If the command is run from Test Server  connecting to any SQL Server instance including Test Server it is running fine.

    Tried: 1) Given the read/write rights on shared folder, to user under which the SQL server service is running on Test Server 

    2) Given the read/write rights on shared folder to everyone.

    Query:

    SELECT DISTINCT * FROM OPENROWSET

    (

    BULK '\\ServerName\input\FileName.csv',

    FORMATFILE='\\ServerName\Format.xml'

    )

    AS FileList

     

    Please provide me with some solution. What can be the reason for such behaviour?

    Tuesday, January 23, 2007 6:01 AM

Answers

  • Hi Madhu,

    thanks for reply.

    i had tried with giving the read/write rights on shared folder to everyone.

    After long analysis i found the error is due to the settings for remote connections for which "using named pipes only" was selected. Changing it to "using TCP/IP only" resolved the issue.

    Tuesday, January 23, 2007 10:52 AM

All replies

  • basically, what the error says is you do not have permission

     on "\\ServerName\input\FileName.csv".

    check the sql service account from which server you are getting error. check whether that service account has the access to that folder

     

    Madhu

    Tuesday, January 23, 2007 8:25 AM
  • Hi Madhu,

    thanks for reply.

    i had tried with giving the read/write rights on shared folder to everyone.

    After long analysis i found the error is due to the settings for remote connections for which "using named pipes only" was selected. Changing it to "using TCP/IP only" resolved the issue.

    Tuesday, January 23, 2007 10:52 AM
  • Hi Jasdeep,

    thanks for the info ... thanks a lot

     

    Madhu

    Tuesday, January 23, 2007 1:58 PM
  • Where was this setting regarding named pipes vs TCP?

    I'm getting this same error using the Bulk Load task in an SSIS package.
    I assumed it was due to file permissions on the csv file, and was trying to figure out which credentials were being used to access the file.

    I'm very willing to entertain any answers people can give me though?

    Friday, February 02, 2007 1:45 AM
  • This option is present in SQL Server 2005 -> Configuration Tools -> SQL server surface area configuration.

    First make sure u give the read access to the account under which service is running.

    Regards,

    Jasdeep

    Friday, February 02, 2007 5:04 AM
  • I have the same problem, but the surface config has both TCP/IP and namepipes enabled. And I still get this 'Cannot bulk load...' error. Any help will be appreciated. Thanks.
    Tuesday, July 29, 2008 4:36 PM
  • The problem has to do with the service account that SQL runs under.  Bulk insert uses the SQL Server service account to access the file share.  In order to access files from a remote machine, you'll need to change the service account from a local account to a domain account.

     

    Monday, August 04, 2008 5:09 PM
  • We are never using a local system account to run SQL services. It is running under the domain account, which is a local admin on both the SQL server, and on the server that is hosting the file share.
    Monday, August 04, 2008 5:23 PM
  •  

    we have domain accounts too, and not locally defined otherwise.

     

    the remote machine has a share with almost god rights.

     

    SEE NEXT POST for our solution.

    Thursday, August 21, 2008 11:24 PM
  •  

    OK: Found the solution:  Under the DB in question:  Under SECURITY --> Server Roles --> BULDADMIN --> add the user in question/ sqladmin, but

    OR Db in question --> security --> logins --> R-Click username --> Server Roles -- check buldadmin.

     

    this will give effective permissions to that user.

     

    IF the sqladmin does, however, not have specific file rights to that server's files/directories, then you'll have to grant them for your Networks, system administrator's account...perhaps that's you or some other person.

    Thursday, August 21, 2008 11:43 PM
  • The user is bulkadmin and sysadmin on the SQL box, still no luck.
    Friday, August 22, 2008 2:06 PM
  • I have a two node cluster.

     

    I have tried all the suggestions from all the replies and I still have the same problem. You mean to tell me no one, is still have this problem?

     

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

     

    ALTER procedure [dbo].[sms_LoadStudentData] AS

    delete dbo.student_load_fjs

    BULK INSERT dbo.student_load_fjs FROM '\\remote_file_server\plan_access\StudentLoad.TXT'

    WITH (

    FIELDTERMINATOR = ';',

    ROWTERMINATOR = '\n'

    )

     

     

    Msg 4861, Level 16, State 1, Procedure sms_LoadStudentData, Line 4

    Cannot bulk load because the file "\\rhett\plan_access\StudentLoad.TXT" could not be opened. Operating system error code 5(Access is denied.).

    Tuesday, August 26, 2008 2:28 PM
  • Just a thought, but this error also occurs when another process has the file open for exclusive access. This is quite common when you have one application that creates the file and then SQL Server reads it in. If SQL Server starts reading in the file before the other process closes the file you get this error. Also some programs have a bug that means they don't close the file properly when they have finished writing. You can tell if this is the case by opening the file with Notepad.exe if the file is locked you get this error "The process cannot access the file because it is being used by another process".

     

    There is a post telling you how to find which process has the file locked here: http://windowsxp.mvps.org/processlock.htm

     

    Friday, October 03, 2008 11:06 AM
  • Same problem.  Nothing from above has worked so far.

    I can run xp_cmdshell "del <the file in question>'  successfully, can

    XP_CMDSHELL 'echo 10/11/2008,a,a,a>> <the file in question>' successfully but still get the access denied message trying to BULK INSERT.  The same exact file loads if it is on my local machine: I created a share and am using the UNC format just fine.

    Tried another share on the network without success. Just don't know enough to figure out the difference.

    I gave full permissions for the remote share as well as the file to the SQL Server service account and myself.  No luck.

     

    Any thoughts from those who figured this out?

    Thank you in advance.

    Rafael

     

    Sunday, October 12, 2008 10:33 PM
  • Hi

     

    This worked for me.  I'm using SQL 2005 and Active Directory...

     

    The service which called the BULK INSERT sp was running under the context of a domain account, however I changed it to use a sql account after reading the article below.  In my opinion and with limited knowledge on 'delegation' the risk associated with allowing the privilege seemed higher than using sql authentication.  

     

    Extract taken from SQL BOL. 

     

    Security Account Delegation

    If a SQL Server user is logged in using Windows authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process. For more information, see Security Considerations for Using Transact-SQL to Bulk Import Data.

    When executing the BULK INSERT statement using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

    To resolve this use SQL Server Authentication and specify a SQL Server login, which uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about enabling a user account to be trusted for delegation, see Windows Help.

     

     

    If you want to use a domain account read the article 'Enable computer and user accounts to be trusted for delegation'  in Windows 'Help and Support'..

     

    Hope this helps...

     

    Thanks

    • Proposed as answer by KD951 Thursday, March 03, 2011 6:03 PM
    Thursday, November 13, 2008 9:00 AM
  • I'm having the same type of issue.  I can bulk load the same file into the same table on the same server using the same login on one workstation, but not on another.  I get this error:

    Msg 4861, Level 16, State 1, Line 1

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

     

    I've checked SQL client versions and they are the same, I've also set the client connection to TCP/IP only in the SQL Server Configuration Manager.  Still this one workstation is getting the error.  Since the same login is being used on both workstations and it works on one  but not the other, the issue is not a permissions issue.  I can also have another user login into the bad workstation and have the bulk load fail, but when they log into their regular workstation it works fine.  Any ideas on what the client configuration issue is?  These are the version numbers for Management Studio:

    Microsoft SQL Server Management Studio 9.00.3042.00

    Microsoft Analysis Services Client Tools 2005.090.3042.00

    Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852)

    Microsoft MSXML 2.6 3.0 5.0 6.0

    Microsoft Internet Explorer 6.0.2900.5512

    Microsoft .NET Framework 2.0.50727.1433

    Operating System 5.1.2600

     

     

    Thanks,

    MWise

    Thursday, November 13, 2008 9:17 PM
  • This worked for me... connected with sa and seemed to work fine.
    • Proposed as answer by Cuffbert Monday, October 18, 2010 3:47 PM
    Friday, November 20, 2009 4:54 PM
  • I logged in with SQL Server Authentication instead of Windows Authentication and it works. Thanks
    Thursday, March 03, 2011 6:02 PM
  • Hi,

    I'm having a very similar problem to yours and you are the only one who has mentioned this problem with clusters. Did you get to solve it? if so, what was the solution?

     

    Cheers


    Cheers,
    Friday, November 25, 2011 5:01 PM
  • for those of you who have stumbled on this thread and are trying to load files from a filetable share this article helped me:

    http://garrettedmondson.wordpress.com/tag/sql-server-2012/

    short version: create a mapped network drive to the share and use the drive letter in the path instead of the share UNC.  For some reason this worked for me.
    • Edited by Deanxzzy Wednesday, February 12, 2014 7:57 PM
    Wednesday, February 12, 2014 7:55 PM