How to fix Error 4870 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 4870 – Bulk Load File Write Error

Why This Error Happens

Error 4870 occurs when SQL Server’s bulk load engine cannot write to the target file because the underlying operating system denies the I/O operation. This is typically caused by insufficient NTFS permissions for the SQL Server service account, the destination disk running out of space, or the target path being blocked by antivirus/EDR software locking the file handle. In some cases, the file or path itself may be corrupted, read-only, or referencing a disconnected network share (UNC path) that the service account cannot authenticate against.

Step-by-Step Resolution Guide

Step 1: Verify NTFS Permissions on the Target Directory

  • Right-click the destination folder (e.g., C:\SQLData\BulkExports\) and open Properties > Security.
  • Confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER or a custom domain account) has Write and Modify permissions.
  • Use PowerShell to grant access if missing:
  icacls "C:\SQLData\BulkExports" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)M"

Step 2: Check Disk Space and File Locks

  • Run Get-PSDrive -PSProvider FileSystem in PowerShell to confirm the target volume isn’t full.
  • Use Resource Monitor (resmon.exe) to check the CPU > Associated Handles tab, searching for the file name to identify any process locking the file.
  • Temporarily disable third-party antivirus/EDR real-time scanning on the SQL data directories to rule out file-lock interference.

Step 3: Validate UNC Paths and Network Share Authentication

  • If the bulk load target is a network path (e.g., \\FileServer\Exports\), ensure the SQL Server service account has explicit share-level and NTFS permissions.
  • Test connectivity manually by running the following from the SQL Server host using the service account context:
  Test-Path "\\FileServer\Exports\testfile.txt"
  • If authentication fails, reconfigure the service to run under a domain account with delegated access instead of a local/virtual account.

Step 4: Restart the SQL Server Service and Re-run the Bulk Operation

  • Restart the SQL Server (MSSQLSERVER) service via services.msc or:
  Restart-Service -Name 'MSSQLSERVER' -Force
  • Re-execute the BULK INSERT or bcp command and monitor the SQL Server Error Log (ERRORLOG) for confirmation that the write succeeded.

Alternative or Verification Steps

  • Verify the fix by manually creating and deleting a test file in the target directory using the SQL Server service account credentials (via runas /user:) to confirm write access outside of SQL Server.
  • If the issue persists, redirect the bulk load output to a local disk path temporarily to isolate whether the root cause is permissions-related or an underlying storage/network fault.