Column Encryption: "Cannot drop column master key because column encryption key is encrypted by it."
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
- SqlPackage or DacFx Version: 162.0.52.1
- .NET Framework (Windows-only) or .NET Core: net core
- Environment (local platform and source/target platforms):
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 Home 10.0 (Build 19045: )
**Steps to Reproduce:**
1. Create a table with a column using a COLUMN ENCRYPTION KEY referencing a COLUMN MASTER KEY
2. Build and deploy the database
3. Change the column encryption key and the column master key to use different values
4. Rebuild and redeploy the database
5. Expected: The data is decrypted according to the old keys and re-encrypted using the new keys
6. Actual: sqlpackage tries to drop the column master key before it drops the column encryption key resulting in an error

Example SQLProject: [Demo.zip](https://github.com/microsoft/DacFx/files/12836686/Demo.zip)
**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
Reproduce the issue with the linked Demo.zip SQL project using SqlPackage against SQL Server 2019, checking the deployment behavior described in the steps. Trace the deployment ordering for the column master key and column encryption key. Done means changing both keys deploys successfully, decrypts existing data with the old keys, and re-encrypts it with the new keys.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- databases, security
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100