SQL Always Encryption in RedGate Source Control

Answers

• Andrew P Redgate › Posts: 194 Silver 3

  November 29, 2017 3:42AM edited November 29, 2017 3:42AM

  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!

  Andrew Pierce
  Technical Sales Engineer
  Redgate Software

  0 · Share on Twitter
• davehahn Posts: 4 New member

  April 9, 2021 2:54PM

  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
  
  
  

  0 · Share on Twitter