User profile for 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 / comments

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 th...

0 votes

@David_D thanks. I was able to get this working. It appears AZDO caps -parallel on foreach to 5 (but I don't see that locally), regardless was able to cut deployment time down by 75% for multi-tenant databases. / comments

@David_D thanks. I was able to get this working. It appears AZDO caps -parallel on foreach to 5 (but I don't see that locally), regardless was able to cut deployment time down by 75% for multi-t...

0 votes

I figured it out.....$dbConn.ConnectionString.UnmaskedValue needs to be referenced, without the additional property, it defaults to the masked value (I wrote out the password and noticed it was *******) / comments

I figured it out.....$dbConn.ConnectionString.UnmaskedValue needs to be referenced, without the additional property, it defaults to the masked value (I wrote out the password and noticed it was ***...

0 votes

New-DatabaseConnection Login Error when just using connection string

I am trying to convert an existing script to be a PowerShell workflow so that I can run deployments in parallel (use case is multiple instances of the same database).When using New-DatabaseConnecti...

2 followers 3 comments 0 votes

How can we help you today?

davehahn

Activity overview