SqlBulkCopy v7 breaking change: now requires metadata visibility for sys.all_columns

[jmbryan4]

Describe the bug

After upgrading from Microsoft.Data.SqlClient v6.x to v7.x, SqlBulkCopy now requires the application SQL login to have metadata visibility sufficient for a query against sys.all_columns.

This appears related to PR #3590, which changed SqlBulkCopy target-column discovery from the previous metadata-discovery path:

SET FMTONLY ON;
SELECT * FROM {table};
SET FMTONLY OFF;

to a query against sys.all_columns in order to support hidden columns, such as those used by temporal tables.

I understand the motivation for the change, and the hidden-column behavior is a real improvement. However, this appears to introduce a runtime breaking change for applications running under least-privilege SQL logins that previously worked with SqlBulkCopy in v6.

In our environment, the application login has permission to insert into the target table, but it does not have the broader metadata visibility needed for the new sys.all_columns query. Our DBA/security team is not willing to grant broader catalog visibility to the application runtime identity.

This is not being reported as a SqlClient security vulnerability. The concern is that v7 changes the permission contract for existing SqlBulkCopy callers and conflicts with least-privilege database policies in hardened environments.

To reproduce

The core repro is:

1. Create a SQL login/user with permission to insert into a target table.
2. Do not grant that login broader catalog metadata visibility.
3. Execute a SqlBulkCopy.WriteToServer(...) call targeting that table using Microsoft.Data.SqlClient v6.x.
4. Observe that the operation succeeds.
5. Upgrade to Microsoft.Data.SqlClient v7.x.
6. Execute the same SqlBulkCopy.WriteToServer(...) call.
7. Observe a permission error related to the new sys.all_columns query.

Example repro:

using System.Data;
using Microsoft.Data.SqlClient;

var connectionString = "Server=localhost;Database=SqlBulkCopyRepro;User Id=bulkcopy_app;Password=your-password;";

var table = new DataTable();
table.Columns.Add("Name", typeof(string));
table.Rows.Add("Example row");

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

using var bulkCopy = new SqlBulkCopy(connection)
{
    DestinationTableName = "dbo.BulkCopyTarget"
};

bulkCopy.ColumnMappings.Add("Name", "Name");

await bulkCopy.WriteToServerAsync(table);

Example database setup:

CREATE DATABASE SqlBulkCopyRepro;
GO

USE SqlBulkCopyRepro;
GO

CREATE TABLE dbo.BulkCopyTarget
(
    Id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_BulkCopyTarget PRIMARY KEY,
    Name nvarchar(100) NOT NULL
);
GO

CREATE LOGIN bulkcopy_app WITH PASSWORD = 'your-password';
GO

CREATE USER bulkcopy_app FOR LOGIN bulkcopy_app;
GO

GRANT INSERT ON dbo.BulkCopyTarget TO bulkcopy_app;
GO

-- Intentionally do not grant broader metadata visibility / catalog permissions.
-- This is the least-privilege configuration that worked with Microsoft.Data.SqlClient v6.x.

Expected behavior

SqlBulkCopy against a normal table that does not require hidden-column handling should not require broader metadata/catalog permissions than were required in v6.

A SqlBulkCopy operation that worked in v6 with a least-privilege application login should not fail after upgrading to v7 solely because the implementation now queries sys.all_columns.

Callers that do not use hidden columns should have a compatibility path that preserves the previous least-privilege behavior.

Further technical details

Microsoft.Data.SqlClient version: v7.x
Previous working Microsoft.Data.SqlClient version: v6.x
.NET target: .NET 10.0
SQL Server version: SQL Server 2017
OS (App): Linux Docker container / Kubernetes
OS (DB): Microsoft SQL Server 2017 (RTM-CU24) (KB5001228) - 14.0.3391.2 (X64) Apr 28 2021 10:32:18 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Additional context

PR #3590 changed SqlBulkCopy column discovery to query sys.all_columns to support hidden columns. That is understandable for hidden-column scenarios, but it also changes the permission contract for existing SqlBulkCopy callers.

This blocks upgrading from v6 to v7 in environments where application logins are intentionally restricted and DBAs are not willing to grant broader catalog metadata visibility to runtime application identities.

Would the SqlClient team consider one of the following mitigations?

1. AppContext switch — expose a switch such as Switch.Microsoft.Data.SqlClient.SqlBulkCopy.UseLegacyColumnDiscovery that restores the previous metadata discovery behavior for callers who do not need hidden-column support.

2. Graceful fallback — attempt the sys.all_columns query first, and fall back to the previous metadata discovery path if a permissions error is returned.

3. Conditional/lazy metadata path — avoid querying sys.all_columns unless hidden-column handling is required or explicitly requested.

Option 2 or 3 would be transparent to callers. Option 1 would at least give affected applications an escape hatch without requiring a DBA security policy change.

References:

• PR introducing the change: Fix | Allow SqlBulkCopy to operate on hidden columns #3590
• Original issue fixed by that PR: SqlBulkCopy - throws error when Hidden columns are specified in the ColumnMappings #1854

[github-actions]

🔍 Triage Summary

Check	Result
Issue type	Bug
Environment	All required environment details provided for investigation
Area	Area\Sql Bulk Copy
Duplicates	Directly references PR #3590 and issue #1854 (originating change); no separate duplicate issue found
Regression	Likely regression from v6.x → v7.x introduced by PR #3590

Analysis

This is a confirmed breaking change introduced in v7 where SqlBulkCopy column discovery was changed from SET FMTONLY ON/OFF to a sys.all_columns catalog query (PR #3590). Applications running under least-privilege SQL logins that only have INSERT on the target table now fail with a permission error on upgrade, even when no hidden-column functionality is needed. This is a P1 regression: it blocks v6→v7 upgrades in security-hardened environments and has no workaround without DBA permission changes. The reporter has proposed three reasonable mitigations (AppContext switch, graceful fallback, or conditional metadata path).

Next Steps

• Assign to Copilot coding agent to investigate SqlBulkCopy column metadata discovery in src/Microsoft.Data.SqlClient/src/ (look at the code changed in PR Fix | Allow SqlBulkCopy to operate on hidden columns #3590).
• Evaluate the three proposed mitigations. The recommended path is Option 2 (graceful fallback) — catch the permissions error from sys.all_columns and fall back to the SET FMTONLY path — as it is transparent to callers and requires no API or AppContext surface changes.
• If Option 2 has unacceptable complexity or correctness risk, implement Option 1 as an AppContext switch following the existing pattern in LocalAppContextSwitches.cs (e.g., Switch.Microsoft.Data.SqlClient.SqlBulkCopy.UseLegacyColumnDiscovery).
• Add a regression test covering a least-privilege login scenario with only INSERT permission on the target table.

Note: This triage summary is auto-generated by an AI agent. The analysis and suggestions above have not been verified by a human maintainer. Please treat as preliminary guidance only.

Generated by SqlClient Issue Auto-Triage for issue #4293 · ● 2.1M · ◷

[edwardneal]

I've just tested the repro code, and was able to reproduce the permissions failure when I manually stripped the permission from the public database role in master.

For completeness' sake: it's also possible to check permissions over the object manually using sys.fn_my_permissions or HAS_PERMS_BY_NAME. These aren't suitable though - sys.fn_my_permissions is supported on all platforms, but early versions of SQL Server allow permissions over that TVF to be revoked; meanwhile, HAS_PERMS_BY_NAME isn't marked as supported in Azure Synapse dedicated SQL pools. The only cross-platform way to know whether we have permissions seems to be to actually run the query.

It's also worth noting that another PR is currently being reviewed (#3677) which builds on the use of sys.all_columns in order to allow $to_id / $from_id / etc. to operate as aliases for SQL Graph columns.

I think the second option (that of catching the exception and falling back) seems safer, and should protect clients from similar regressions in the future. Once a decision's made though, I'll re-validate #3677 under the same permission conditions - I tested with a low-privilege account, but didn't include server-level permission modification scenarios in #3590's test coverage.