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

sanjeev kumar 16 Reputation points
2021-02-09T09:10:11.067+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,747 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,553 questions
0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-02-09T15:38:12.567+00:00

    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.

    1 person found this answer helpful.
    0 comments
  2. Olaf Helper 40,896 Reputation points
    2021-02-09T10:36:01.243+00:00

    5 = Access is denied

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

    0 comments
  3. sanjeev kumar 16 Reputation points
    2021-02-09T11:09:33.28+00:00

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

  4. Erland Sommarskog 101.1K Reputation points MVP
    2021-02-09T23:14:39.247+00:00

    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.

  5. MelissaMa-MSFT 24,176 Reputation points
    2021-02-10T05:52:00.113+00:00

    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.