Continuous Schema Drift (Idempotency Failure) caused by NCHAR(0xFFFE) / NCHAR(0xFFFF) bypassing
- 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
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