In the previous article I explain the full setup of a mechanism to automate the access management of databases across various servers.

In this article, I’ll start with the basics: the access and how to store it, or the SQL logins and KeyVault.

Logins

There are a few different logins required in this setup

  • login (e.g. job_login) used to
    • unlock the deploy login upon request (used by the Azure function)
    • lock the deploy logins that are still open after the requested unlock window (used by the Elastic Job Agent across the different databases. For this reason the same login should exist in the different databases)
  • logins for each database you want to lock/unlock. These have the necessary rights to make modifications (DDL and DML statements), for simplicity, we consider them db_owner here, but you can restrict to the rights you want you deployment team to have. So, in total as many different accounts as there are databases, e.g. TEST_EU_DB1 needs account testeudb1deploy , PROD_EU_DB1 needs account prodeudb1deploy (as example for naming these accounts).
  • (out of scope and trivial) : developer accounts for dev, where the initial development work is done. The Dev databases are not locked.

job_login

The job_login has to be created on each Azure SQL database server and needs the same password on each server:

-- in master
CREATE LOGIN job_login WITH PASSWORD = 'mystrongpassword'
GO

CREATE USER job_login
GO

CREATE DATABASE SCOPED CREDENTIAL job_login WITH IDENTITY = 'job_login',
    SECRET = same password as login; 
GO

--set rights for the logins in master 
exec sp_addrolemember 'dbmanager', 'job_login' 
exec sp_addrolemember 'loginmanager', 'job_login'

Deploy logins

This login should have the required access to one specific database, e.g. the database TEST_EU_DB1 on the server AXIS0-TEST-EU.database.windows.net

-- in master
CREATE LOGIN testeudb1deploy WITH password = 'mystrongpassword'
GO
-- in database TEST_EU_DB1
CREATE USER testeudb1deploy
GO
EXEC sp_addrolemember 'db_owner', 'testeudb1deploy'
GO
ALTER LOGIN testeudb1deploy DISABLE
GO

Key Vault

Secrets

Setup an Azure Key Vault, where you will store the passwords of above logins as secrets in the key vault

Once you created the secret for job_login, you’ll want to copy the URL to this secret as we’ll use it in the next steps.