Always Encrypted with SQL Server Data Project

Question

Always Encrypted with SQL Server Data Project

David Betteridge

Hall of Fame

Points: 3264
More actions
October 14, 2019 at 3:14 pm

#3688528
Hi,
We have a small database (just 3 tables) where we wish to use Always Encrypted to encrypt the data in a few of the fields in each table. I've managed to successfully encrypt the data using the SSMS wizard and powershell scripts, and I can read/write from our application.
I'm now looking for some advice for the best way to deploy any future changes to the database. For the other databases we are using a SSDP projects which generate dacpac files which we then push out from Azure Dev Ops.
This issue I currently have is that we have three different environments (dev, staging and production) which each have their own keys (and certificates).
I have to mark the columns as encrypted within the project in order for the database to be deployed. However to mark the columns as encrypted I also have to specify the COLUMN_ENCRYPTION_KEY. (CEK_Auto1 in the example below)
```
    [SupplierName]                    NVARCHAR (255) COLLATE Latin1_General_BIN2  ENCRYPTED WITH (
     COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
     ALGORITHM = N'AEAD_AES_256_CBC_HMAC_SHA_256',
     ENCRYPTION_TYPE = RANDOMIZED
    ) NOT NULL,
```
This means that I have to also add a file containing
```
CREATE COLUMN ENCRYPTION KEY [CEK_Auto1]
WITH VALUES
(
     COLUMN_MASTER_KEY = [CMK_Auto1],
     ALGORITHM = N'RSA_OAEP',
     ENCRYPTED_VALUE = 0x016E000001630.....
);
```
But I wish the ENCRYPTED_VALUE to be different on each environment. Is this possible as I don't think this command support parameters.

thanks
David
- This topic was modified 5 years, 1 month ago by David Betteridge.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

heb1014 Hall of Fame Points: 3790 More actions · Answer 1

I've not has a business requirement for this so I haven't done this yet. However, I'm wondering if dynamic SQL would work. Have you tried that?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Here's the issue. AE requires the cert to be installed for the system and access to resources outside of SQL Server. This also does an encryption outside of the db, so you need a process working outside the db. The dacpac and dev takes place inside the db. While you might be able to deploy this with a dacpac and perhaps some pre/post, I think you are better off with scripting from PoSh that allows admins to set this up (one time event) for the instance.

AFAIK, some of the AE setup can't be done with T-SQL (outside of dynamic SQL). I'd rather have an admin run a PoSh script for each new CEK/CMK I need.

David Betteridge Hall of Fame Points: 3264 More actions · Answer 3

Hi,

Thank you for the reply. I'm happy doing the initial encryption manually, it's more the on going maintenance of the database that's my issue.

From my tests so far, the columns in sqlserver project have to match the columns in the real database. Which means that once I encrypt the column in the database I also have to specify the columns are encrypted in the ssdp.

That's when I hit my issue, in order to specify the column as encrypted, I also have to specify the encryption key etc.

I expect I'm overlooking something simple, like a flag to say ignore encrypted columns when deploying the dacpac.

thanks

David

David Betteridge Hall of Fame Points: 3264 More actions · Answer 4

My last post just gave me an idea!

It looks like you can set

/p:ExcludeObjectTypes=ColumnEncryptionKeys;ColumnMasterKeys

when calling sqlpackage.exe. I'll try that!

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

That should work, though I would think you could name the CMK/CEK the same on each system, even if different values.