SQL PaaS and Octopus Deploy

Legacy Program Management
1

Hi Support

We’ve been using OctopusDeploy for approximately 3 years now for mainly on-premises deployments (SQL/APP/WEB).

For SQL deployments we have a standardised Deploy.ps1 which takes our build’s dacpac(s) and uses sqlpackage/dacFX to publish/script/report etc. against target SQL instances.

We’d like to adopt a similar approach to Azure SQL (PaaS) databases - i.e. use the same basic Deploy.ps1 to publish our dacpacs to target SQL PaaS instances in Azure.

We can achieve this by having a dedicated tentacle server on-premises and allowing that tentacle access to Azure on port 1433 so that this tentacle can connect to Azure instances using a standard connection string.

The problem we then face is, we lose the segregation of environments - i.e. the firewall rule which allows our tentacle server access to Azure exposes all of Azure (e.g. dev, test, pre-production and Production instances all accessible from this one tentacle)…

It would therefore be possible for a deployment in the “test” environment to have a mis-configured connection string which would inadvertently (or deliberately!) target the production SQL instance.

it is possible to add a prerequisite check step to our Octopus process that would validate the connection string against current environment (e.g. environment = “test” and $connection contains Azure equivalent of “test”)…The problem here being, there is currently no way to make a deploy step mandatory.

  1. Is there a best-practice approach to deploying from on-premises Octopus to SQL PaaS (which retains segregation of environment)
  2. Are there plans to introduce mandatory process steps (which cannot be disabled at deployment time)

Any advice on this would be much appreciated,
thanks

1 Like
2

Ideally, would would be able to setup database connections to Azure SQL in the same way as we can set up Accounts for software deployments.

4

Hi,

That’s an interesting question. I believe I understand your scenario.

One approach I can think of is to install a separate Tentacle, which is then added to only your Production environment. This Tentacle would run as the domain account which had access to your Production Azure SQL database. You could grant your other Tentacle account access to all your other environments, but only the blessed Tentacle would have access to the Production database, and it would only be included in Production deploys. Note that this Tentacle could be installed on the same server as the existing Tentacle (or even on the Octopus server). It is the account that it executes as which provides the isolation.

Would that work for you? Please let me know if I didn’t explain that well.

I’m not sure that would solve the problem? There would still be nothing stopping a user from inadvertently typing the production connection string into a DB connection configured for another environment.

Are you using username\password or Windows integrated authentication?

Regards,
Michael

5

Thanks Michael,

unfortunately, we have one public-facing IP address and access to Azure for this tentacle server is allowing all traffic on port 1433 to all of Azure…

So, no matter how many tentacles we have - there is effectively only one route to Azure and we fall prey to the possibility of incorrect configuration (or even malicious config, e.g. the rogue insider who would like to publish our database to their own personal azure subscription for example :frowning: )

Can you address my second point please?
2) Are there plans to introduce mandatory process steps (which cannot be disabled at deployment time)
…or indeed at design time! (forgot that important part)

I know the top user-voice request at the moment mentions this in comments and there are 2 other user-voice requests pertaining to the same thing - this would allow us to put enforcement steps into our octopus process that would restrict the above behaviour.

Details of those user-voice requests as follows:

mentioned in comments of the top user-voice request:
”: https://octopus.com/blog/rfc-composite-step-templates
and first raised about 4 years ago here:
https://octopusdeploy.uservoice.com/forums/170787-general/suggestions/6511629-add-permissions-to-stop-deployers-from-skipping-st

Can you please provide an update on whether mandatory steps are on the roadmap at all?

thanks,
Paul

6

Paul,

We agree with the idea of marking steps as required, and are willing to commit to this work. I have created an issue which you can track, and I’m going to push to have this implemented as soon as possible. I can’t give you an exact date, but please feel welcome to follow up and check on it’s progress (keep us honest!).

I’m not sure what you mean here? These steps would be able to marked as required\not-required when editing the deployment process. If someone has permissions to edit the deployment process, then they would be able to also set these fields. The key is the person deploying the release would not be able to then skip required steps. The general principle we apply in scenarios like this is permission to modify the deployment-process should only be granted to as small a group as possible, and must be fully trusted. They have permission to author scripts that will execute on all servers, anything else seems like a lesser super-power.

To jump back to the technical details of your problem, I think we are mis-understanding each other slightly. I’ll walk through my proposed setup, and we can see where we diverge:

  1. You have multiple accounts configured in Azure Active Directory. For now let’s call them:

    • SqlAdminDev (granted access to non-prod databases)
    • SqlAdminProd (granted access to prod database)

  2. On the server with port 1433 open to Azure, you have two Tentacle services installed:

    • AzureSqlDevTentacle (running as SqlAdminDev)
    • AzureSqlProdTentacle (running as SqlAdminProd)

  3. The Tentacles are configured in Octopus as:

    • AzureSqlDevTentacle is added to your non-prod environments in Octopus.
    • AzureSqlProdTentacle is added to only your prod environment

This assumes you are using Windows-integrated-authentication in your connection strings. With this approach, even if someone mis-configured the connection string in a development environment to point to the production database, it wouldn’t work because the account that Tentacle is executing as doesn’t have access to the production SQL Server. Only deployments to the Production environment will run on AzureSqlProdTentacle which is executing as the SqlAdminProd account.

Have I missed a piece of the puzzle?

7

Thanks Michael,
would be great to see this implemented :slight_smile:

for the design time consideration; I was seeing this as an admin task to decree that a step would be mandatory, and that normal project contributors (our devs basically) would not be able to alter this aspect. If this does not fit in well with the current security model I can understand the reluctance, but we just want to make this as water-tight as possible. If devs could remove a step or mark it as not-mandatory then it defeats the object as far as we’re concerned (if we had a malicious developer wanting to deploy to a personal azure subscription for example - we can assume this is a very small risk, but cannot ignore it)

As for the SQLPaaS credentials - we’re deploying dacpacs with SQL-based credentials in the target connection string and deploying to multiple SQL PaaS instances with different SQLAdmin credentials, so this method would not work - however we do have checks and balances in place that will give us the assurance we need.

Mandatory steps is just the missing piece which would ensure our checks and balances always run :slight_smile:

Thanks again - I’ll keep an eye on progress with this issue

8

Hi Paul,

We have implemented the ability to mark steps as required. This will ship in Octopus version 2018.4.0, which will be available early April.

I hope this helps!

9

forgot to say thanks for this!