SQL Always Encryption in RedGate Source Control

Bluecar85

I am utilizing SQL 2016 AlwaysEncrypted for customer data (SSN, Birthdates, etc), etc. We are trying to avoid saving Encryption Value key in the RedGate Source Control, since this would defeat whole purpose of encryption. What is a good solution?

Example: (we do not want to store encrypted_value in our project)

CREATE COLUMN ENCRYPTION KEY [ColumnEncryptionKey1]
WITH VALUES
(
COLUMN_MASTER_KEY = [ColumnMasterKey1],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE =0x016E000001630075007200720065006E00740075007300650072002F006D0079
My Proposed Solution:

1) Column Master Key certificate: is required to be saved in Application Web Server or Azure Data vault; (Not the SQL Production Server).

Would I write post-deployments script which utilizes (Dynamic SQL, C#, Powershell, etc)? This would grab the encryption key path from the Web server text file or Vault file, place into statement below, and run.

CREATE COLUMN MASTER KEY [ColumnMasterKey1]
WITH
(
KEY_STORE_PROVIDER_NAME = N'[SomeProviderName]',
KEY_PATH = N'[SomeKeyPath]'
)
2) The Column Encryption Key: is required to be saved on SQL Server. It can be autogenerated using web resources: MSDNGenerateColumnEncryptionKey or MSFTColumnEncryptionKey

-- Generate a column encryption key, encrypt it with the column master key to produce an encrypted value of the column encryption key.

$encryptedValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings
$encryptedValue
Or

-- Generate a column encryption key, encrypt it with the column master key and create column encryption key metadata in the database.

$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
After generating using powershell, place into script.

CREATE COLUMN ENCRYPTION KEY [ColumnEncryptionKey1]
WITH VALUES
(
COLUMN_MASTER_KEY = [ColumnMasterKey1],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = Extract value from Powershell Scripts Above)
Is this the proper solution?

Andrew P

I've done some testing on this with the WorldWideImporters database and the MSDN walkthrough here.

The table script for that object is:
CREATE TABLE Purchasing.Supplier_PrivateDetails
(
SupplierID INT
CONSTRAINT PKFK_Purchasing_Supplier_PrivateDetails PRIMARY KEY
CONSTRAINT FK_Purchasing_Supplier_PrivateDetails_Suppliers
FOREIGN KEY REFERENCES Purchasing.Suppliers (SupplierID),
NationalID NVARCHAR(30) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = WWI_ColumnEncryptionKey, ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NULL,
CreditCardNumber NVARCHAR(30) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = WWI_ColumnEncryptionKey, ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NULL,
ExpiryDate NVARCHAR(5) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = WWI_ColumnEncryptionKey, ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NULL
);
GO

When you look at in SQL Source Control you will see the following :
:
-- Columns

CREATE TABLE [Purchasing].[Supplier_PrivateDetails]
(
[SupplierID] [int] NOT NULL,
[NationalID] [nvarchar] (30) COLLATE Latin1_General_BIN2 NULL,
[CreditCardNumber] [nvarchar] (30) COLLATE Latin1_General_BIN2 NULL,
[ExpiryDate] [nvarchar] (5) COLLATE Latin1_General_BIN2 NULL
) ON [PRIMARY]
GO
-- Constraints and Indexes

ALTER TABLE [Purchasing].[Supplier_PrivateDetails] ADD CONSTRAINT [PKFK_Purchasing_Supplier_PrivateDetails] PRIMARY KEY CLUSTERED ([SupplierID]) ON [PRIMARY]
GO
-- Foreign Keys

ALTER TABLE [Purchasing].[Supplier_PrivateDetails] ADD CONSTRAINT [FK_Purchasing_Supplier_PrivateDetails_Suppliers] FOREIGN KEY ([SupplierID]) REFERENCES [Purchasing].[Suppliers] ([SupplierID])
GO


So the tool looks at it as if it is a normal table - the scripts folder does not contain the encryption keys, and when you deploy, it will treat it like a normal table.

You would need to handle the setup for Always Encrypted using Prescripts - and for that reason, I would recommend using ReadyRoll for AlwaysEncrypted table deployment (you can configure Pre and Post scripts in ReadyRoll)

Hope that helps!

davehahn

I know this is an old post but struggling through always on encryption with Redgate myself and just wanted to share some of the issues I'm running into.

What I'm struggling with is I can't create the keys through T-SQL--they are different for every environment and every tenant instance that the database is deployed to and keys are created in Keyvault.  e.g. each deployment has a different master key that needs to be created based on the Azure key.  So any key creation in the project is not really possibly (unless there is some way to inject PowerShell into T-SQL, which I'm not aware of).

My thought was to separate the key creation from the consumption--as long as the key name is the same, the migration script can reference it, having a process in place to ensure the key is created before build and deployment.   Unfortunately that is a problem that has to be solved 3X--once for local development  builds (where it creates a local keystore key with a PowerShell in a pre-deployment task), once in the AzDO build which requires the key to pass validation, and then a deployment process to handle creating the keys.

Where I am struggling is the AzDO build.  First, the Redgate task uses "Debug" configuration for some reason and doesn't appear to be controllable, so it's trying to execute the pre-deployment task in AzDO.  I had to put some additional checks to get past that, works, but not sure why it is using Debug.

Then, the real problem, since it creates a temporary database all internal to the AzDO task, there is no way to create a key before hand external to the build.  My thought was to use a provided SQL Server temporary database instead, where I could create the key, but the first thing it does is wipe everything, including the keys--so this process doesn't seem like a viable option.  Would be nice to control things that do not get purged.

So I'm left with it looking like the key must be created in T-SQL for the AzDO build to work.  I'm assuming that anything that runs in the build will make it's way into the package though, which I don't want, because as part of the deployment (or DB creation itself or a supporting security pipeline) will create the key which is not going to be the ultimate key.  I'm thinking that the pre-deployment script could check to see if the key exists, if no, create a local keystore static key to get past the build, otherwise do nothing.  This might work, assumes that the deployment pipeline that creates the key must be run before hand (otherwise it would create the static key), but I think that's reasonable.

So my situation is a little more complex--with multi-tenant and Azure Keyvault, but at minimum I would think the multi-environment issue would be something everyone would encounter, you absolutely would not want to use the same keys in production as development for example, so how to handle within Redgate?

Any thoughts or ideas would be wonderful