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

In this blog post, we are going to discuss how to solve the following error “Cannot bulk load because the file could not be opened. Operating system error code 5 (Access is denied.)”. Below you will find the reason why this error happened and the way to solve it.

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

From the very beginning, it is necessary to mention that this error message can appear as a false and misleading error. This error message as a false error can appear when you use the T-SQL command “BULK INSERT” to import data from the text file to the SQL Server table. Let’s reproduce Cannot bulk load because the file could not be opened. Operating system error code 5 (Access is denied.) error message using the next script to create a sample data:

Create Database Adventureworks
GO
USE Adventureworks
GO
CREATE TABLE [dbo].[MyData](
 [Id] [int] NOT NULL,
 [Name] [char](200) NULL,
 [ModDate] [datetime] NULL
)
GO
INSERT INTO MyData VALUES (1, 'Data.1', GETDATE())
GO
INSERT INTO MyData VALUES (2, 'Data.2', GETDATE())
GO
INSERT INTO MyData VALUES (3, 'Data.3', GETDATE())
GO

That was the first part of our test script. It’s time to export this data to the text file. We can do it using bcp (bulk copy program utility) command. And then, we will import the data back from the table. Use the following bcp.exe command to export the data.

bcp.exe Adventureworks..MyData out “c:\Temp.txt” -c -T -S.\SQL2014

After we have executed this command let’s insert the data back into the table using the next script:

USE Adventureworks
GO
BULK INSERT MyData
FROM 'C:\Temp'
WITH
(
 KEEPNULLS,
 FIRSTROW = 2,
 FIELDTERMINATOR ='\t',
 ROWTERMINATOR ='\n'
)

After you will receive the following error message:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\Temp" could not be opened. Operating system error code 5(Access is denied.).

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

Solution

The reason this error occurred was that we specified “C:\Temp” without the “.txt” extension. It was a false and misleading error. So to all you need to do to solve it just specify the correct name “C:\Temp.txt”.

But what to do if you specify the correct name and still receive this error? The reason that you receive this error message is that you are using SQL Server Authentication and the SQL Server hasn’t accessed the bulk load folder. All you need to do is to give SQL Server access to the folder.

Here is how you can grant all necessary permissions. Go to the folder and right-click, select “Properties” and open the “Security” tab. Click “Edit” then “Add” and press the “Advanced” button on the new window. After clicking “Find Now”. The last step you need to do is to find something like SQLServerMSSQLUser$UserName$SQLExpress and click ok, to all the dialogs opened.

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

Press “OK” to apply all new permission settings.

That is all. That were two of the most popular cases of how to solve this issue and the reasons why it happens.

10 thoughts on “Cannot bulk load because the file could not be opened. Operating system error code 5 (Access is denied.)”

  1. I have almost the same problem, I have 2 servers, the Server SQL and the Server File. I execute the script in SQL Server Management Studio from my laptop with Windows authentication, but I got the error 4861: Cannot bulk load because the file… but, If I execute the script in the server SQL it works. I can access to the server file putting the path in the explorer in my lap and the server sql, just I can’t execute it in my laptop

    Any idea?

    Reply
  2. After applying full permission to SQLServerMSSQLUser this issue not resolved.getting same error.

    Can anyone please help me out.

    Reply
  3. The frustrating issue is that the so called BULK INSERT only reads from sql server drive not from your local drive. If you put the file on your server folder it will work

    Reply
  4. @Bon, we use SQL External Data Source to allow us to not require the file on the server.
    I’m facing a slightly different issue though in that the file is constantly “in use by another process”:
    Core .Net SqlClient Data Provider: Cannot bulk load because the file “myExternalFile.csv” could not be opened. Operating system error code 32(The process cannot access the file because it is being used by another process.)

    Works fine when running inside Azure Data Studio.

    Reply
  5. I’ve got a publicly accessible share, I’ve enabled full access to multiple SQLServer accounts ( I don’t have the one you specify but something similar). Add the permissions to the folder itself & to the share.

    Still no go.

    Reply
  6. You use SQL Server Authentication Account to login.
    1. open SQL Server Configuration Manager -> SQL Server Services -> in “log on” tab, change the Account “NT SERVICE\MSSQLSERVER” to account (e.g in Active Directory) with the password.
    Restart the service.
    2. repeat the steps for the service “SQL Server Agent” and use the account in the step 2, and restart the service
    3. grant file system permission to this account (Folder -> properties -> Security tab-> Edit and then add)

    Reply

Leave a Comment