In this series, I’ll detail a process to automate giving and removing access to databases. Not just a single one, but different databases over different servers.

Introduction

The use case is that of a system that has DEV-TEST-PROD setup, where developers have access to DEV, but no one should have permanent access to TEST or PROD.

That seems manageable, but copy this setup for a multitude of databases, and those are also located on different server.
E.g. you’re running Navision as ERP for different companies in different Azure regions.

In this case, also access to one test database should not give you access to other test databases. All logins should be unique and opened for a specific task.

Without the implementation of a CI/CD workflow (which is not available), the change deployment is manual, and developers will from time to time need access to a specific TEST database as well as to a specific PROD database. However, this access has to be approved by an application owner. Also when the testing has completed, the application owner wants to approve the changes that are moved to PROD.

The implementation guarantees that at any time (outside of the window of approved access) all TEST and PROD environments are locked down, so developers can’t accidentally make the odd “quick” change that accidentally brings down the system. It enables you to have controlled access to your databases and in addition, you are able to log (for audit purposes) who has had access to which database and for how long.

It’s quite an elaborate setup, so that’s why I split this in a small series.

Overall design

The below diagram illustrates the overall design and the different components involved. Each of these I’ll detail in separate posts (they’ll become available when they’re finished):

Process flow

That’s all there is to it, let me break this down in the various components:

  • A Microsoft Forms form is used by the developers to request access to a specific environment and database, with the reason why the access is needed, for how long, the change management ticket reference, etc…
  • The submission of the form triggers a PowerAutomate flow for the approval process. You’ll also get a notification in Teams for these requests. The approver(s) can choose to approve the access, and an email is sent about the decision. In this case I also distinguish between a request for Test or Prod, calling different Azure Functions
  • The Azure Function call will unlock a login in a specific database and will retrieve the credentials for doing so from Azure KeyVault
  • At the same time, an elastic job is running every 10min across the different servers to check for logins that are open and have passed their expiry time. When a login is found, the elastic job disables the login again

The next post will detail each for these components, so that by the end of this series you can set this up yourself.