microsoft / microsoft/DacFx

Column Encryption: "Cannot drop column master key because column encryption key is encrypted by it."

Open
#349 4 comments 0 reactions 0 assignees View on GitHub
area: encryption bug
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
![image](https://github.com/microsoft/DacFx/assets/51305664/63b77dda-a265-4e44-94b3-1e38b1e146ca)

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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.