microsoft / microsoft/DacFx

Continuous Schema Drift (Idempotency Failure) caused by NCHAR(0xFFFE) / NCHAR(0xFFFF) bypassing

Open
#796 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

XML validation and mutating during deploy

* **SqlPackage or DacFx Version:** [e.g., 162.1.173.1 - Run `sqlpackage.exe /Version` to get yours]
* **.NET Framework (Windows-only) or .NET Core:** [e.g., .NET 6 / .NET 8]
* **Environment (local platform and source/target platforms):** [e.g., Windows 11 publishing to Azure SQL Database]

**Steps to Reproduce:**

1. Connect to a target database and execute the following dynamic SQL to bake raw high non-characters directly into `sys.sql_modules`:

```sql
DECLARE @sqlC NVARCHAR(MAX) =
N'CREATE OR ALTER PROCEDURE dbo.SchemaCtl_RawCtl_High AS BEGIN
-- Evaluate NCHAR here so the RAW BYTES land in sys.sql_modules
DECLARE @s NVARCHAR(50) = N''hi' + NCHAR(0xFFFE) + NCHAR(0xFFFF) + N'lo'';
SELECT @s AS body_with_high_noncharacters;
END';
EXEC sp_executesql @sqlC;
GO

```

2. Run `sqlpackage /Action:Extract` to pull this database into a `.dacpac`.
3. Run `sqlpackage /Action:DeployReport` comparing the `.dacpac` to the database.
4. Open the `DeployReport.xml` and observe that DacFx flags `[dbo].[SchemaCtl_RawCtl_High]` as modified, breaking idempotency.

**Expected Behavior:**
The `DeployReport` should be completely empty (no drift) because the DACPAC was just extracted from/published to the exact same database.

**Actual Behavior / Root Cause Analysis:**
The procedure gets stuck in an infinite schema drift loop. The root cause appears to be a disconnect between how DacFx handles XML and how the deployment parser handles EOF characters:

* Standard XML 1.0 explicitly forbids `0xFFFE` and `0xFFFF`. However, during extraction, DacFx successfully writes the DACPAC's `model.xml` by setting `XmlWriterSettings.CheckCharacters = false`.
* During publish/deploy, DacFx reads `model.xml` with `XmlReaderSettings.CheckCharacters = false`, successfully loading the illegal bytes into memory.
* **The Failure:** When the script is sent to the target, the BatchParser (or ADO.NET layer) silently mutates or strips the `0xFFFF` bytes (likely interpreting `0xFFFF` as `EOF`).
* Because the published procedure text is silently mutated on the server, the subsequent `DeployReport` compares the unmutated DACPAC XML against the mutated `sys.sql_modules` definition and registers a drift.

**Did this occur in prior versions? If not - which version(s) did it work in?**

(DacFx/SqlPackage/SSMS/Azure Data Studio)
Unknown, but likely exists in all prior versions due to foundational XML handling and BatchParser behavior.

Contributor guide

Open the contributing guide

Research direction

Reproduce the issue with the supplied dynamic SQL, then run sqlpackage /Action:Extract and /Action:DeployReport. Inspect the extracted model.xml, DeployReport.xml, sys.sql_modules, and the BatchParser or ADO.NET path described in the report. Done means the extracted DACPAC compared with the same database produces an empty DeployReport without changing the procedure text.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
databases, devtools
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.