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

Question

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

sanjeev kumar 21

Team,

I am trying to bulk load .sql file from Network storage using Kerberos authentication but getting below error. Please help me out.
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot bulk load because the file <filename>could not be opened. Operating system error code 5(Access is denied.).

0 comments

7 answers

Your answer

Answer 1

Tom Phillips 17,786

Bulk loads run in the context of the SQL Server SERVICE account, not the user running the command. The SQL Server service account must have read access to the location.

0 comments

Answer 2

MelissaMa-msft 24,246 Moderator

Hi @sanjeev kumar ,

Welcome to Microsoft Q&A!

Please provide the BULK INSERT statement in order to avoid any syntax issue.

Since you configured the Kerberos, please validate below points to make sure it is working fine:

The client computer and the server computer must belong to the same Windows domain or be in a trusted domain.
The client must be able to correctly obtain the IP address and FQDN/Netbios of the SQL Server server, and the DNS function on the network must be effective.
On the domain controller, there is one and only one SPN registered by the current service account of SQL Server.

You could also search the DMV sys.dm_exec_connections after connection created. The DMV has a column called auth_scheme. This column will show whether the corresponding connection uses NTLM or Kerberos.

Besides, please validate below points:

The data file must be shared between the two computers.
To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form of \ Servername \ Sharename \ ... .
The user account that is used by SQL Server must have been granted the permissions that are required for reading the file on the remote disk.

You could also refer this article for more details.

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

sanjeev kumar 21 Reputation points

2021-02-10T07:14:36.877+00:00
Hi Melissa,

Please find my answers below for your questions:

Client computer and server computer are in trusted domain

From client computer, there is no connectivity issue via ipadress and fqdn

I am not sure how many SPNs are registered with SQL server service account name but i think its more than one.

I don't have rights to check sys.dm_exec_connections

Data file is shared between two computers and permissions in place.

Below is the query to perform bulk insert

BULK INSERT <table-name> FROM
'\<server-address>\<folder-name>\xyz.sql' WITH (FIELDTERMINATOR = ' ',ROWTERMINATOR = '0x0a',MAXERRORS=100);

Please let me know if you need more details.
MelissaMa-msft 24,246 Reputation points Moderator

2021-02-10T07:53:28.19+00:00

Hi @sanjeev kumar ,

Thanks for your update.

I could search some examples with bulk insert ..from .csv or .txt file till now.

Per my knowledge, I am not sure whether it is also working with .sql file.

Please have a try to exchange the .sql file to .csv or .txt file and have another try.

Regarding to the SPN, if you have enough permission of setspn, you could refer the article I provided earlier to list, add,or remove all SPNs.

Best regards
Melissa
sanjeev kumar 21 Reputation points

2021-02-10T08:42:42.477+00:00

Previously it was working fine with .sql file. Actually there was some patch update from Microsoft side to win server and SQL server. After this, I was unable to connect to SQL server. So I had to update the jdbc driver and java version.

I was able to connect after doing jdbc update and java version update. you can find more details with the following link as i had raised that issue with Microsoft community as well.
https://learn.microsoft.com/en-us/answers/questions/246759/error-while-connecting-to-ms-sql-server.html

But now, I am getting this bulk load issue.
MelissaMa-msft 24,246 Reputation points Moderator

2021-02-12T07:52:11.567+00:00
Hi @sanjeev kumar ,

Could you please provide any latest update?

As mentioned by Erland, we recommend you to check with your admin team or IT team whether there are more than one SPN registered in DC.

If it is more than one, you need to remove the extra one and keep unique one.

Please also connect someone else who has the right to search this using the DMV sys.dm_exec_connections which is a fast way to check whether Kerberos is working or not.

select auth_scheme,* from sys.dm_exec_connections

Best regards
Melissa
sanjeev kumar 21 Reputation points

2021-02-12T10:10:31.097+00:00

Hi @MelissaMa-msft ,

we executed above query and found that Kerberos authentication is being used. So this basically seems delegation issue and also, trusted for delegation is set to false. We have asked AD team to look into this and do the required changes as per Microsoft Learn. I will keep you posted regarding this.

Thanks
Sanjeev
sanjeev kumar 21 Reputation points

2021-02-15T15:55:22.597+00:00

Hi @MelissaMa-msft

We did the changes but still the same issue. Is there any other way to rectify this issue?

Regards
Sanjeev
MelissaMa-msft 24,246 Reputation points Moderator

2021-02-16T06:40:11.653+00:00

Hi @sanjeev kumar ,

Thanks for your update. Your Kerberos seems to be working well.

Previously it was working fine with .sql file. Actually there was some patch update from Microsoft side to win server and SQL server. After this, I was unable to connect to SQL server. So I had to update the jdbc driver and java version.
I was able to connect after doing jdbc update and java version update. But now, I am getting this bulk load issue.

It seems to be an upgrade issue or compatibility issue. You could search if any one else face the same issue with you. Or you could submit your issue to the Microsoft feedback.

Besides, you could check whether this article is helpful to you.

Best regards
Melissa

Answer 3

Erland Sommarskog 138.4K MVP Volunteer Moderator

Tom Phillips's answer is incorrect. If you connect with Windows authentication, SQL Server will impersonate you when you access the file.

However, unless you are logged in directly on the machine running SQL Server, there is a double hop, and Kerberors must be set up correctly.

If you are logging on to SQL Server with SQL authentication, access is indeed by the SQL Server service account.

sanjeev kumar 21 Reputation points

2021-02-10T03:52:41.073+00:00

In first scenerio, I am connecting to SQL server using sqljdbc42 api via code with Kerberos authentication mechanism and getting the bulk load error.

In second scenerio, I am connecting to SQL server with window authentication in SSMS and getting the same bulk load error.

The account which I use to connect has having all permission like bulkadmin and access to the folder where file is present.
I am not able to collect what is the exact issue.

Answer 4

sanjeev kumar 21

The account which I use to connect to SQL server, that has already access for the folder where file is present.

Andreas Kreuzberg 96 Reputation points

2021-02-09T12:44:11.933+00:00

Hi,
maybe your Login got the right permission. But the account, who startet the sql server didn't got the right permission to load the file from the nas.

Please check, if the sql server service account got the right permission.
sanjeev kumar 21 Reputation points

2021-02-10T05:08:25.447+00:00

I am using Kerberos authentication to connect to SQL server via sqljdbc API. Do I still need to provide access to shared folder to SQL server service account. When I connect SQL server using window AUTH in SSMS, I get the same error.

Answer 5

Olaf Helper 47,626

5 = Access is denied

You don't have read permissions for the file on the NAS = Access denied.

0 comments