Incorrect Deployment Order when removing Column Encryption Key

[asrichesson]

• 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.
3. Remove the column encryption key, master key, and the encrypted column's reference to the encryption key in your sqlproject.
4. Redeploy the database using sqlpackage.
5. Expected: The table is decrypted, the column encryption key is dropped, and finally the master key is dropped
6. Actual: sqlpackage tries to drop the column encryption key before the table column is decrypted resulting in an error.

   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:

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)

[zijchen]

@skpadhy33 this seems like an AE issue, can your team take a look? Thanks.

[skpadhy33]

Hi,

Unless the property '/p:DropObjectsNotInSource' is not set to $true, the deployment using SqlPackage does not attempt to drop the CEK(s) and CMK(s).

Hence the following command does not throw the error: .\SqlPackage /Action:Publish /SourceFile:$dacpacPath /TargetConnectionString:$connStr /DiagnosticsFile:$logFilePath

But, the following ccommand reproduces the issue, which we are looking into: .\SqlPackage /Action:Publish /SourceFile:$dacpacPath /TargetConnectionString:$connStr /DiagnosticsFile:$logFilePath /p:DropObjectsNotInSource=True

I understand that the aim is not to simply decrypt the column but also drop the CEK and CMK in the target database by publishing the dacpac. In that case, there is a simple workaround to achieve this.

1. Start with the target database having the CEK, CMK and the table with encrypted column.
2. Deploy the dacpac (which does not have the CEK and CMK, and the table columns are in plain text) without setting the property '/p:DropObjectsNotInSource' as follows. This decrypts the encrypted column but does not drop the CEK and CMK. .\SqlPackage /Action:Publish /SourceFile:$dacpacPath /TargetConnectionString:$connStr /DiagnosticsFile:$logFilePath
3. Now re-deploy the same dacpac to the target database with the property '/p:DropObjectsNotInSource' specified as $true as follows. This will successfully drop the CEK and the CMK. .\SqlPackage /Action:Publish /SourceFile:$dacpacPath /TargetConnectionString:$connStr /DiagnosticsFile:$logFilePath /p:DropObjectsNotInSource=True

@asrichesson , hope this helps if not tried already. We are looking into this issue and let you know for any updates.

Thanks.