Cannot Bulk Load, Access Is Denied (Error 4861): It Is the Service Account, Not You

This is the 2am ETL failure that makes no sense. The job has run every night for a year. You remote onto the server, open the exact file in Notepad, and it opens. You run the exact statement from the job step and it fails with 4861 and “Access is denied”. Nothing you can see about your own access is wrong, because your own access was never the question.

The short version is that BULK INSERT and OPENROWSET(BULK...) read the file from inside the SQL Server process, and which Windows identity that process uses depends entirely on how you connected. It is the same lesson as cannot open backup device, one layer across: the engine is a service, and a service has its own account.

Which Account Actually Opens the File

There are only two answers, and your connection decides which one you get. MS Docs states it plainly under security account delegation on the BULK INSERT reference: a login using SQL Server authentication cannot be authenticated outside the Database Engine, so the connection to the data is made using the security context of the SQL Server process account. A login using Windows Authentication can read only the files that that user can read.

• SQL Server login. The file is opened by the service account. Your own rights on the file are irrelevant.
• Windows login, local path. The file is opened as you, because SQL Server impersonates you and the hop is zero machines long.
• Windows login, UNC path, from a client machine. The file is opened as you only if Kerberos delegation is configured. This is the double hop, and it is the hardest case. It has its own section below.

Two statements tell you where you are. The first names the account, the second names your own authentication scheme:

-- Which Windows identity the engine runs as
SELECT servicename, service_account, status_desc
FROM   sys.dm_server_services;

-- And how YOUR session got in
SELECT SUSER_NAME() AS login_name, auth_scheme
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID;

servicename                          service_account                      status_desc
------------------------------------ ------------------------------------ -----------
SQL Server (MSSQLSERVER)             NT Service\MSSQLSERVER               Running
SQL Server Agent (MSSQLSERVER)       NT Service\SQLSERVERAGENT            Running
SQL Server Launchpad (MSSQLSERVER)   NT Service\MSSQLLaunchpad            Running

login_name      auth_scheme
--------------- -----------
HPAI01\Peter    NTLM

Two things to take from that output. The service account is NT Service\MSSQLSERVER, a virtual account, which is the default on a modern install and the account that will be refused on any folder that was locked down by hand. And auth_scheme says NTLM, which matters later: an NTLM connection cannot delegate, so a Windows login going over a UNC path on an NTLM connection will fail no matter how the share is permissioned.

Note that the Agent runs as a different account. A BULK INSERT inside an Agent job step using a proxy, or a job step that connects with a SQL login, can resolve to a third identity again. Check the job step, not just the instance.

The Same Statement, Two Logins, Seconds Apart

This is the whole post in one capture. A folder, C:\Imports, holding a three row CSV. icacls C:\Imports\sales.csv lists three entries, HPAI01\Peter:(I)(M), BUILTIN\Administrators:(I)(F) and NT AUTHORITY\SYSTEM:(I)(F), and nothing at all for the service account. Everything else is identical: one instance, one table, one statement, run twice in the same minute from the same machine.

BULK INSERT dbo.Sales
FROM 'C:\Imports\sales.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, MAXERRORS = 0);

Run on a connection made with a SQL Server login:

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

Run on a connection made with Windows Authentication, nothing else changed:

rows_loaded
-----------
          3

That is why “but I can open the file” is never evidence. You opened it as you. The failing statement opened it as a service. This is also why the error survives moving the file, copying it, or running the job manually from your own session, which often appears to fix it and then fails again the next night when the scheduled step runs under the original connection.

Read the Operating System Error Code First

4861 is a wrapper. The number in brackets is the real finding, and it is the part people skip on the way to editing permissions. Every row below is a capture from the same instance against the same table, with the only variable being the path or the permission:

The folder row is the one that wastes the most time, so here it is on its own. With the folder readable by everyone who needs it, pointing the statement at the directory rather than the file gives you this:

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

Access is denied, on a folder nobody has denied anything on. Windows will not let a process open a directory as a data stream, and the refusal surfaces as the same code as a permission failure. If the path in the message has no file extension on the end, stop and look at the path before you look at the ACL.

The 3 cases are worth one more line, because they cover three different mistakes that all look the same. A folder that does not exist, a drive letter that exists only in your own logon session, and a UNC share that is not there all return path not found. Mapped drives are the common one: a Z:\sales.csv path came back as Operating system error code 3(The system cannot find the path specified.), because drive letters are per user and per session and the service account has neither your user nor your session.

Grant the Right Account, on the Folder

Once sys.dm_server_services has named the account, the fix is one command on the server. Read and execute on the folder, inherited by what is in it, is enough for an import that only reads:

# Use the exact string sys.dm_server_services returned.
icacls "C:\Imports" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)(RX)"

# Then prove it landed on the file, not just the folder
icacls "C:\Imports\sales.csv"

processed file: C:\Imports
Successfully processed 1 files; Failed processing 0 files

C:\Imports\sales.csv NT SERVICE\MSSQLSERVER:(I)(RX)
                     HPAI01\Peter:(I)(M)
                     BUILTIN\Administrators:(I)(F)
                     NT AUTHORITY\SYSTEM:(I)(F)

With that one entry in place and nothing else changed, the SQL login that was refused a moment earlier loads all 3 rows. Take the entry away again and the 4861 comes straight back.

Four details that decide whether this holds up:

• Grant on the folder with (OI)(CI), not on today’s file. Tomorrow’s file is a new object. A grant on a single file fixes one night and fails the next, which is how these tickets come back a week later with “it worked for a while”.
• Verify the entry reached the file. If the folder had inheritance disabled, the new entry may not be where you think it is. The second icacls above is not ceremony, it is the check.
• Read is enough to read, but not to use ERRORFILE. ERRORFILE writes rejected rows out, so that path needs write access for the same account. With read and execute only, the import fails twice over, and the filename in the message is the giveaway that this is the write side rather than the read side.
• Virtual accounts are local to the machine. NT Service\MSSQLSERVER exists only on that server, so it can never be granted on a file share held by another machine. If the data lives on a share, the service needs a domain account or a gMSA, and that changes the conversation to the double hop. MS Docs covers the account types on configure Windows service accounts and permissions.

That third point is worth showing on its own, because it is the version of this error that arrives after you think you have fixed it.

ERRORFILE Needs Write, and Never Names the CSV

Read and execute granted, the data file opens, and the statement still fails on the path it was going to write rejected rows to:

Msg 4861, Level 16, State 1, Server HPAI01, Line 3
Cannot bulk load because the file "C:\Imports\rejects.txt" could not be opened. Operating system error code 5(Access is denied.).
Msg 4861, Level 16, State 1, Server HPAI01, Line 3
Cannot bulk load because the file "C:\Imports\rejects.txt.Error.Txt" could not be opened. Operating system error code 5(Access is denied.).

Two messages, neither of them naming the CSV. Read the filename in the message every time: it tells you which file the engine was reaching for, and here it is the one it wanted to create.

When the File Is on a Share: the Double Hop

This is the case that generates the most confused tickets, and it is the one I have to be straight about: the three machine double hop is documented here, not reproduced here. It needs a client, a server and a file server as three separate hosts with a domain behind them, and the lab behind this post is one box. Everything in this section comes from MS Docs and from how the two reproducible halves behave.

The shape is this. You are on your workstation. SQL Server is on another server. The CSV is on a file share, which is a third. You connect with Windows Authentication, so SQL Server wants to open the file as you. To do that it has to pass your identity on to the file server, and passing an identity on to a third machine is delegation. NTLM cannot do it. Kerberos can, if it has been configured to.

MS Docs says it directly on the BULK INSERT security notes: running the statement from one computer, inserting into SQL Server on a second, and specifying a data file on a third by UNC path “you might receive a 4861 error”, and the resolutions are to use SQL Server Authentication so the service account’s profile is used, or to configure Windows to enable security account delegation.

The first check costs nothing and tells you whether delegation is even possible. Run the auth_scheme query from Which Account Actually Opens the File and read the second column. If your own connection is on NTLM, there is no Kerberos ticket to forward and the double hop cannot work, whatever the share permissions say.

NTLM there means stop and fix that first, usually a missing or duplicate SPN. KERBEROS means the ticket exists and the question moves on to whether the service account is trusted for delegation to the file server. Both of those live on Kerberos vs NTLM in SQL Server, which has the SPN registration and the delegation paragraph, and the SPN failures themselves show up as SSPI handshake failed long before anyone tries a bulk load.

There is a shortcut, and it is the one most shops take. Switch the import to a SQL Server login and grant the service account on the share. That replaces a delegation problem with a permissions problem, and permissions problems can be fixed by one person in one afternoon. It needs a domain account or a gMSA as the service account rather than a virtual account, because a virtual account cannot be granted anything on another machine.

One thing I could test on a single box is worth passing on. Pointing a SQL login’s BULK INSERT at a UNC path on an administrative share, which the virtual service account has no rights to use, did not return access denied. It returned operating system error 3, path not found, for a file that was certainly there. So on a share, do not read error 3 as proof the path is wrong. It can equally mean the account never got far enough to be told no.

OPENROWSET(BULK) Hits the Same Wall and Reports 4860

OPENROWSET(BULK...) reads files through the same path as BULK INSERT, so it has the same identity rule and the same failure. What it does not have is the same error number. Against the folder state that produced 4861 above, the same SQL login running an OPENROWSET got:

SELECT a.BulkColumn
FROM   OPENROWSET(BULK 'C:\Imports\sales.csv', SINGLE_CLOB) AS a;

Msg 4860, Level 16, State 4, Server HPAI01, Line 2
Cannot bulk load. The file "C:\Imports\sales.csv" does not exist or you don't have file access rights.

4860 is the sibling message, and sys.messages shows why it is so much less helpful: “Cannot bulk load. The file "%ls" does not exist or you don’t have file access rights.” It offers two possibilities and no operating system code to choose between them. 4861 at least hands you the Windows error number.

So if you are searching for 4860 and getting nowhere, the trick is to re run the same path through BULK INSERT into a throwaway table, purely to get the operating system code out of it, and then come back to the operating system code table. BULK INSERT returns 4860 too, but for a different fault: a filename that is genuinely wrong, or a relative path, both of which produced 4860 state 1 on the lab while the permission failure produced 4861.

Both statements are documented together on MS Docs under import bulk data by using BULK INSERT or OPENROWSET(BULK…), which is the page to read if you are choosing between them rather than debugging one.

What Not to Do

Do not grant Everyone or Authenticated Users to make it go away. It will work, and it will stay in place for years, and the folder usually holds customer data that arrived by SFTP. Grant the one account the engine actually uses. You already know its name from the first query on this page.

Do not change the service account to LocalSystem or to a domain admin. This is the fix that gets offered most often and it is the worst one on the list. The bulk load permission is not a small permission: a login with bulkadmin and no other server role at all reads whatever the service account can read. On the lab, a non sysadmin login in bulkadmin read C:\Windows\win.ini through OPENROWSET(BULK...) without any extra rights. Widening the service account widens that in exactly the same proportion.

Do not move the file into the SQL Server data folder. It works, because the service account owns that folder, and it quietly puts an untrusted inbound file next to the databases. Keep imports in an imports folder and grant that folder properly.

Do not use a mapped drive letter, ever. It will work when you test it interactively and fail under the service, because drive letters are per user and per session. Use a UNC path, or a local path on the server.

Do not retry the job in a loop for an operating system error 32. That is a file still being written, and a retry loop will either win a race or import half a file. Fix the handover: write to a temporary name, rename when complete, and have the import look for the final name.

Common Questions