CODEWITHSUNDEEP
sql-serverazuresql-server-data-toolsalways-encryptedsqlproj
Alavan
Alavan

Reputation: 21

sqlproj build error about computed column referencing an always-encrypted column, but it works on the DB

I have this table

CREATE TABLE [dbo].[LawFirm] 
(
    [LawFirmId]   INT            IDENTITY (1, 1) NOT NULL,
    [EntityId]    INT            NOT NULL,
    [Name]        VARCHAR (100)  NOT NULL,
    [Description] VARCHAR (2000) NULL,
    [EIN]         VARCHAR (15)   COLLATE Latin1_General_BIN2  ENCRYPTED WITH (
     COLUMN_ENCRYPTION_KEY = [CEK_AZ_EIN],
     ALGORITHM = N'AEAD_AES_256_CBC_HMAC_SHA_256',
     ENCRYPTION_TYPE = DETERMINISTIC
    ) NULL,
    [PhoneNumber] VARCHAR (30)   NULL,
    [AddressId]   INT            NULL,
    [EINOnFile] AS CASE WHEN EIN IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END PERSISTED,

    PRIMARY KEY CLUSTERED ([LawFirmId] ASC),

    CONSTRAINT [FK__LawFirm__Address__640DD89F] 
        FOREIGN KEY ([AddressId]) REFERENCES [dbo].[Address] ([AddressId]),
    CONSTRAINT [FK__LawFirm__EntityI__6501FCD8] 
        FOREIGN KEY ([EntityId]) REFERENCES [dbo].[Entity] ([EntityId]),
    CONSTRAINT [UQ_LawFirm_EntityId] UNIQUE NONCLUSTERED ([EntityId] ASC)
);

And when I use Schema Compare, I get a build error:

Error SQL71595:
Column [dbo].[LawFirm].[EIN] cannot be encrypted because the computed column [dbo].[LawFirm].[EINOnFile] depends on it.
Database C:\DEV\GIT\AFFFPortal\Database\dbo\Tables\LawFirm.sql

Except that this is allowed, and it currently works on our Azure database because the ENCRYPTION_TYPE = DETERMINISTIC. This allows for NULL checks in such a computed column, so a principal without permission to access the Azure key can still see if the EIN is on file. In this case, the EIN was added and fully encrypted before the EINOnFile column.

Is there a way to suppress this error so I can use Schema Compare?

I'm also open to suggestions on how to move away from .sqlproj files and Schema Compare (because it kinda sucks).

I did try removing the EINOnFile column, and it built successfully. If there is no good solution, I'm prepared to do this and just live with it, but I'd like it to fully work.

I also checked for updates to Visual Studio 2022, and thus, SSDT.

Upvotes: 2

Views: 73

Answers (0)

Related Questions