Access Denied –How To Prevent a Failure Mid-Deploy

Hello!

As part of any decent path-to-live, it is obviously crucial to deploy to other environments. This is crucial because not only do we need to test the changes being made, but just as importantly that the deployment will succeed. An ideally these environments should match as closely to production as possible. The less difference there is between production and all environments up to production, the greater the chances a deployment will succeed. Sounds simple, and perhaps even trivial, but if there is one thing that always, and I mean always catches deployments out, it’s permissions. Chances are production permissions for deployment accounts are tightly locked down and controlled by Ops/SecOps/DBA’s etc, but in other environments, even PreProd, accounts can be given carte blanche to do whatever they want. This is fine in dev/test, but further up the path to live, not so much.

What we need to do then is create as many checks as we can prior to changes being made in order to ensure that if a deployment is to fail, then it won’t be because of permissions.

Recently a client was using salt/AssistDeploy to complete a deployment to production, and they were hit by this very issue – in every environment up to Production, the deployment account had sysadmin permissions. And so deployments would fail midway through because of a lack of permissions. This meant that jobs ended up without steps in them, which is not great. Fortunately they had disabled the job and were affecting anything else except this one job that ran nightly. so w had time on our side to get permissions right.

And again, like any decent deployment process, we sat down and discussed what could be done differently next time. What became abundantly clear out of this was that

both Modules should explicitly state what permissions are required if an account is not a sysadmin. This was rectified in the readmes of each of the modules.

If an account is not sysadmin and does not meet the minimal permissions, then we should fail a deployment before anything is changed and not midway through.

This second issue requires a little more effort. For AssistDeploy, the minimal permissions that the account needs to be in the “ssis_admin” role on SSISDB. Simples. But the permissions for salt are more tricky – to alter Job Owner the account has to be sysadmin. To change a job, the account has to own the job. To change schedules the account doesn’t own, it has to be the owner of the schedule. An account needs to be able to have access to a proxy included in the “RunAs” element. There’s more examples like this, and so it took time to sort it.

An alternative might have been to use a “WhatIf” switch – that is, check what needs to be changed but not change it. This won’t work for two reasons -

The “WhatIf” still won’t execute anything, and it’s execute permissions we need to check.

Certain changes further on in the deployment require changes at the beginning to have been done - consider mapping environment variables to project parameters in the SSIS Catalog: this cannot be done without the project parameters being created first.

So with a bit of trial and error we now have the ability to check the permissions prior to a deployment being executed. Both modules now have a “Test-CurrentPermissions” function. This function will check whether or not the user is a sysadmin on the instance. If it is, all is right with the world and we can continue with the deployment no issues.

However, if the account is not a sysadmin, the PowerShell will check that the account has the correct minimal permissions. If it does, great, let’s kick off a deployment. If not then and error is thrown, the deployment fails, but nothing has been changed. There is an additional check with salt, in that it checks that the SQL Agent Service is up and running. It surprised me that an account was able to check what permissions it had, however it certainly is useful.