In order to unlock one of the deploy logins, you need a mechanism that does this without providing access to someone to the database to actually unlock that login.

The goal is to issue an ALTER LOGIN xyz-deploy ENABLE statement. You also want to keep track that this login was enabled, and for how long this should remain opened. For this last part, we’ll use an audit table and wrap this login in a stored procedure

Unlocking objects

Tracking table

Keep track of all the logins and their status on a specific database server with a tracking table. This table needs to be available in every server in the master database, in the dba schema (custom schema).

CREATE SCHEMA dba 
GO
 
CREATE TABLE [dba].[Unlocked_Logins]( 
[Login_Name] [nvarchar](50) NOT NULL, 
[Unlocked_Date] [datetime] NOT NULL, 
[Expiry_Date] [datetime] NOT NULL, 
[Locked_Date] [datetime] NULL 
)

Keep track of each request in a record. When unlocking the time of unlock will be logged, the requested expiry date and a field is foreseen to log when the login was locked again. Initially this will be blank.

Unlock stored procedure

The following procedure will insert a request for a specific login and it’s requested expiry time. It needs to be present on every server in the master database in the dba schema.

CREATE PROCEDURE dba.sp_unlock_login @login_name nvarchar(50), @expiry int = NULL 
AS 
BEGIN 
    DECLARE @sql_txt nvarchar(1000); 
DECLARE @unlock_date datetime; 
    DECLARE @expiry_date datetime; 
 
set @expiry = coalesce(@expiry,60) 
    set @unlock_date = getdate() 
set @expiry_date = DATEADD(mi,@expiry,@unlock_date)  
 
SET @sql_txt = 'INSERT INTO dba.[Unlocked_Logins] ' + 
  '(Login_Name, Unlocked_Date, Expiry_Date) VALUES ' + 
  '(''' + @login_name + ''',''' + 
          CONVERT(varchar, @unlock_date, 120) + ''',''' +  
  CONVERT(varchar, @expiry_date, 120) + ''')'  
 
EXECUTE sp_executesql @sql_txt 
 
SET @sql_txt = 'ALTER LOGIN ' + @login_name + ' ENABLE' 
print 'Unlocking ' + @login_name 
 
EXECUTE sp_executesql @sql_txt 
 
END 
GO

Permissions

To make sure the job_login can modify the table, add following permissions to the tracking table.

grant select on dba.unlocked_logins to job_login 
grant update on dba.unlocked_logins to job_login 
grant insert on dba.unlocked_logins to job_login 

grant execute on dba.sp_unlock_login to job_login

Azure Function

Let’s go ahead and create the function that can be called to unlock a specific login.

After you created a function app, go to the Functions blade and click Create.

Choose to Develop this in the portal and pick the HTTP trigger template, with authorization level Function.

In the run.ps1 script, you use a logic like below (this is a function only for the test environment, you’ll have a separate function for Prod

using namespace System.Net

# Input bindings are passed in via param block.  
param($Request, $TriggerMetadata)
    $instance_info = @{
       "brazil" = @{server="testeu"; database="brazil_db"; login="brdeploy"}
       "germany" = @{server="testeu"; database="germany_db"; login="dedeploy"}
       "spain" = @{server="testeu"; database="spain_db"; login="esdeploy"}
       "france" = @{server="testeu"; database="france_db"; login="frdeploy"}
       "japan"= @{server="testeu"; database="japan_db"; login="jpdeploy"}
       "us"= @{server="testeu"; database="us_db"; login="nadeploy"}
       "india"= @{server="testeu"; database="india_db"; login="indeploy"}
    }

$body = "Function didn't execute, missing arguments."
$statusCode = [HttpStatusCode]::BadRequest

$instanceName = $Request.Query.InstanceName
$environmentName = $Request.Query.EnvironmentName
if ($instanceName -and ($environmentName -eq "UAT")) {
    $expiryTime = $Request.Query.ExpiryTime
    if(-not $expiryTime) {
        $expiryTime=60
    }
    
    # Required parameters for unlocking
    $SQLServer = $instance_info[$instanceName].server
    $SQLDBName = $instance_info[$instanceName].database
    $loginName = $instance_info[$instanceName].login
    $pwd = $env:job_login_pwd
    if($connString -and $loginName){
        $sqlConn = New-Object System.Data.SqlClient.SqlConnection
        $sqlConn.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = False; User ID = job_login; Password = $pwd;"
        $sqlConn.Open()
        $sqlcmd = $sqlConn.CreateCommand()
        $sqlcmd.Connection = $sqlConn
        $query = "exec dba.sp_unlock_login @login_name = '$loginName', @expiry = $expiryTime "
        $sqlcmd.CommandText = $query
        $sqlcmd.ExecuteNonQuery()
        $body = "Function executed successfully."
        $statusCode = [HttpStatusCode]::OK
    }
}

# Associate values to output bindings
Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = $statusCode
    Body = $body
})

In short, the above function has an array of configuration values specific to each database. It will check if it’s being executed for the correct environment and a default expiry time of 60 minutes is set.

A connection is made to the right database and the procedure sp_unlock_login is executed for a certain login and setting a specific expiry time.

Get a copy of the function URL that looks like this : https://axisi0-af01.azurewebsites.net/api/UnlockUser?code=xxxxxxxxxbEboAmetqd8LmUGOsKJ3AzFufnJvjA==

Configuration

In the Function App configuration, you’ll supply the job_login password in the app settings, with a reference to the KeyVault

This is done by adding the configuration and for the value provide

@Microsoft.KeyVault(SecretUri=https://axis0-secrets.vault.azure.net/secrets/joblogin/xxxx03f8ab4xxxxxxxa64ca97d1)

Any time you change the KeyVault, this parameter will also have the latest version.

Permissions

You’ll have to give permissions to the azure function app that should be able to read the secrets.

I’ve configured this with Access Policies:

The function only needs Get on Secrets

For the principal name, select your azure function app e.g. test-axis0-af from the list. You can only select it, when you already created the function app.