Incorrect Deployment Order when removing Column Encryption Key
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
- SqlPackage or DacFx Version: 162.3.566.1
- .NET Framework (Windows-only) or .NET Core: .NET Core
- Environment (local platform and source/target platforms): windows 11, Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64) Jul 19 2021 15:37:34 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 22631: ) (Hypervisor)
**Steps to Reproduce:**
1. Set up a sqlproj with a table with a column encrypted using a column encryption key and master key
2. Deploy the database using sqlpackage.
4. Remove the column encryption key, master key, and the encrypted column's reference to the encryption key in your sqlproject.
5. Redeploy the database using sqlpackage.
6. Expected: The table is decrypted, the column encryption key is dropped, and finally the master key is dropped
7. Actual: sqlpackage tries to drop the column encryption key before the table column is decrypted resulting in an error.
```diff
Updating database (Start)
Dropping Column Encryption Key [DB_kv_cek]...
An error occurred while the batch was being executed.
Updating database (Failed)
-*** Could not deploy package.
-Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 33287, Level 16, State 8, Line 1 Cannot drop column encryption key 'DB_kv_cek' because the key is referenced by column 'MyTable.MySecretColumn'.
-Error SQL72045: Script execution error. The executed script:
-DROP COLUMN ENCRYPTION KEY [DB_kv_cek];
```
Example script:
```sql
PRINT N'Dropping Column Encryption Key [DB_kv_cek]...';
GO
DROP COLUMN ENCRYPTION KEY [DB_kv_cek];
GO
PRINT N'Dropping Column Master Key [DB_kv_cmk]...';
GO
DROP COLUMN MASTER KEY [DB_kv_cmk];
GO
PRINT N'Starting column encryption.';
GO
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[MyTable])
RAISERROR (N'Rows were detected in the table. The schema update to alter column encryption is terminating because it requires migration of data to client and back to server.', 16, 127)
WITH NOWAIT;
GO
DROP TABLE [dbo].[MyTable];
GO
CREATE TABLE [dbo].[MyTable] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[MySecretColumn] VARCHAR (15) NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK] PRIMARY KEY CLUSTERED ([ID] ASC)
);
GO
PRINT N'Completing data encryption for ''[dbo].[MyTable]''.';
```
**Did this occur in prior versions? If not - which version(s) did it work in?**
(DacFx/SqlPackage/SSMS/Azure Data Studio)
Contributor guide
Research direction
Start with the sqlproj reproduction and run deployment through SqlPackage against SQL Server 2019. Inspect the generated deployment script around dropping the column encryption key, decrypting MyTable.MySecretColumn, and dropping the column master key. Done means the table is decrypted before the dependent keys are removed and the redeployment succeeds.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases, tooling
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 30/100