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

by

Troubleshooting Guide: SQL Server Error 1740 – Cannot Encrypt Sparse Column in a Table with a Column Set

Why This Error Happens

This error occurs because SQL Server 2025 does not support encrypting a sparse column (such as with Always Encrypted) when the parent table also contains a sparse column set (the XML-based consolidated representation of all sparse columns). The column set acts as a computed, denormalized view of all sparse columns, and encryption cannot be selectively applied to an individual sparse column while this aggregate structure exists. Essentially, the conflict is a schema design limitation rather than a permissions or connectivity issue.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Column Set

  • Run the following query to locate the column set and associated sparse columns on the affected table:
SELECT name, is_sparse, is_column_set 
FROM sys.columns 
WHERE object_id = OBJECT_ID('YourTableName');
  • Note the name of the column marked with is_column_set = 1, as this must be removed before encryption can proceed.

Step 2: Drop the Column Set From the Table

  • Before altering the schema, back up the table or export data using bcp or SELECT INTO to a staging table.
  • Drop the column set using:
ALTER TABLE YourTableName DROP COLUMN YourColumnSetName;
  • Confirm no application code or views reference the column set XML output, as this will break those dependencies.

Step 3: Reapply Always Encrypted to the Sparse Column

  • Open SQL Server Management Studio (SSMS 21) or use PowerShell’s SqlServer module on Windows Server 2025.
  • Re-run the Always Encrypted wizard or T-SQL ALTER TABLE ... ALTER COLUMN statement targeting only the specific sparse column:
ALTER TABLE YourTableName 
ALTER COLUMN YourSparseColumn ADD ENCRYPTED WITH 
(COLUMN_ENCRYPTION_KEY = YourCEK, ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256');
  • Validate that the column still retains the SPARSE attribute after encryption by checking sys.columns again.

Step 4: Rebuild Dependent Objects

  • Recreate any views, indexes, or computed columns that previously relied on the dropped column set.
  • Update application connection strings or drivers (ODBC 18+/JDBC 12+) to ensure they recognize the new encrypted column metadata.

Alternative or Verification Steps

  • Verify the fix by querying sys.columns to confirm is_column_set = 0 and is_sparse = 1 coexist with encryption metadata in sys.dm_sql_wire_encryption_table or sys.columns.encryption_type.
  • If dropping the column set is not feasible due to application dependency, consider storing the sparse column’s encrypted value in a separate linked table instead of using a column set on the same table.