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

by

Troubleshooting Guide: Error 4143 – “The bulk openrowset provider is not a valid target for %.*ls”

Why This Error Happens

This error occurs when OPENROWSET(BULK...) is used in a syntactically invalid context within a T-SQL statement, such as attempting to use it directly inside an INSERT, UPDATE, or as a standalone target instead of as a row source in the FROM clause. SQL Server 2025 strictly validates that the BULK provider can only act as a source of rows being read into a query, not as a destination for data modification. This frequently happens when legacy scripts written for older bulk import patterns are ported without adjusting the required INSERT INTO ... SELECT * FROM OPENROWSET(BULK...) structure.

Step-by-Step Resolution Guide

Step 1: Review the T-SQL Syntax Context

  • Confirm that OPENROWSET(BULK 'filepath', ...) is placed inside the FROM clause of a SELECT statement.
  • Invalid: INSERT OPENROWSET(BULK 'C:\Data\file.csv', ...) VALUES (...).
  • Valid: INSERT INTO TargetTable SELECT * FROM OPENROWSET(BULK 'C:\Data\file.csv', SINGLE_CLOB) AS DataFile.

Step 2: Correct the Query Structure

  • Rewrite the statement so OPENROWSET(BULK...) only supplies data, never receives it.
  • Example corrected query:
  INSERT INTO dbo.ImportedData (Col1, Col2)
  SELECT Col1, Col2
  FROM OPENROWSET(BULK 'C:\ImportFiles\data.csv', FORMATFILE = 'C:\ImportFiles\format.xml') AS Source;
  • Ensure a table alias (e.g., AS Source) is always applied after the OPENROWSET call.

Step 3: Validate File Path and Permissions on Windows Server 2025

  • Check that the SQL Server service account has Read access to the folder, e.g., C:\ImportFiles\.
  • Use icacls C:\ImportFiles /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)R in an elevated PowerShell/Command Prompt to grant access if needed.
  • Confirm the file is not blocked by Windows (right-click file → Properties → Unblock, if downloaded from the internet).

Step 4: Check for Ad Hoc Distributed Queries Configuration

  • Run the following to ensure the feature is enabled (required for OPENROWSET):
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
  RECONFIGURE;
  • Restart the SQL Server 2025 service via SQL Server Configuration Manager after applying changes.

Alternative or Verification Steps

  • Run the corrected query in SQL Server Management Studio (SSMS) and confirm rows are successfully inserted without Error 4143 appearing in the Messages tab.
  • If the error persists, use BULK INSERT instead of OPENROWSET(BULK...) as an alternative syntax:
  BULK INSERT dbo.ImportedData FROM 'C:\ImportFiles\data.csv' WITH (FORMATFILE = 'C:\ImportFiles\format.xml');