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.).

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.

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.