Azure REST API for the Ephemeral Deploy Packager Process

1 Azure REST API Settings

Liquibase Enterprise uses the Azure REST API to perform a point-in-time restore during packaging for Azure SQL Managed Instances. The Deployment Packager uses the Azure REST API to manage backup and restore operations for the reference database.

You can authenticate with the Azure SQL Database REST API using certificates and a Client Secret. In your Azure REST API settings, you can choose Certificate Authentication or Client Secret Authentication. Also, the following attributes are available in the Datical DB GUI:

Azure Client ID
Azure Tenant ID
Azure Client Secret (for Client Secret Authentication only)
Azure Resource Group
Azure Subscription ID

The REST API Client Secret is a sensitive credential. For this reason, it is strongly recommended that this credential be provided at runtime in an environment variable and not stored in the GUI. For more information, see the Runtime Credentials documentation.

Azure REST API Settings

Azure REST API settings contain the information about Azure SQL Managed Instance (subscriptionId, tenantId, and resourceGroup) and service principal (password and appId, which are Azure Client Secret and Client ID).

Azure REST API settings are only required for Packager processes to support restore and backup operations. You don’t need to configure the Azure REST API settings to execute other commands such as Deploy, Forecast, and others.

Client Secret Authentication

A Client Secret Authentication requires two service principal attributes and the Azure SQL Managed Instance information – Azure Client Authentication and Client ID. Thus, you need to create a service principal to get a bearer token and initiate a point-in-time backup for Azure SQL Managed Instance.

An Azure service principal is an identity created for use with applications, hosted services, and automated tools to access Azure resources. The roles assigned to the service principal have control over which resources can be accessed and at which level. For more information, see Create an Azure service principal with the Azure CLI or Create a service principal using the Azure portal.
A bearer token is a security token that grants access to a protected resource. For more information, see OAuth 2.0 and OpenID Connect protocols on the Microsoft identity platform.

You can create a service principal through the Azure portal or CLI. You must have an Owner role and specify it along with the resource group.

az login

az ad sp create-for-rbac -n "service_principal_name" --role Owner --scopes 
/subscriptions/a365ee0452e-67hr-497d/resourceGroups/dev

Changing "service_principal_name" to a valid URI of 
"http://service_principal_name", which is the required format used for 
service principal names

Creating 'Owner' role assignment under scope '/subscriptions/a365ee0452e-67hr-497d/resourceGroups/dev'

The output includes credentials that you must protect. Be sure that you do not include
these credentials in your code or check the credentials into your source control. 
For more information, see https://aka.ms/azadsp-cli

{

  "appId": "et78acie-some-id",

  "displayName": "service_principal_MI",

  "name": "http://service_principal_MI",

  "password": "bth6-some-password",

  "tenant": "fd564a-some-tenant"

}

Once a service principal is successfully created, ensure the following attributes are available:

appId (Client ID)
password (Client Secret)

This configuration focuses on a single-tenant application run within only one organization. You must have your permissions set to register an application with your Azure AD tenant and assign a role to the application in your Azure subscription. Also, when you register an application through the Azure portal, an application object and service principal is automatically created in your home directory or tenant.

Using Client Secret Authentication

To use Azure Active Directory Client Authentication in the CLI, you need to run the newDbDef command. The newDbDef command creates a new dbDef based on the key and value pairs specified as arguments. The newDbDef command includes the Azure REST API authentication option called azureRestAuthenticationOption, which is required for Azure Active Directory Client Authentication (Azure SQL Managed Instance). The needed value for azureRestAuthenticationOption is clientSecret:

hammer newDbDef DbDefClass SqlServerDbDef dbType AZ_SQLMI 
azureRestAuthenticationOption clientSecret name Source username username_example 
password password_example databaseName DA defaultSchemaName dbo defaultCatalogName 
catalog_example contexts some_context labels some_label 
dbDefType standard storageOptionCollectedAtSnapshot true hostname 127.0.0.0 port 8080 
instanceName test_instance applicationName test_app

There is no need to submit a username and password when you use Azure Active Directory Authentication.

If you do not have the Datical DB project, create it with the project_creator.groovy script. The project_creator.groovy script reads information from a set of input files and generates a project directory in your workspace containing the required configuration files. Use the hammer command to execute the project_creator.groovy script with the files located in the Datical DB installation package: hammer groovy project_creator.groovy <jdbc_drivers_dir> <provisioning_files_dir> <datical_workspace_dir> <project_name> [replace] [zip] [testConnections].

To use Azure Active Directory Client Authentication in the Datical DB GUI, follow these steps:

Open Datical DB and select your deployment plan.
Select the Azure SQL Managed Instance connection type and enter your hostname, port, application name, database name, instance name, username, and password.
Select the Test Connection button to check the connection to your database.
Select the View & Configure Settings button, choose Client Secret Authentication in the Client Authentication dropdown, and then enter all values.
Select OK to set the connection.

Now you can use Datical DB functionalities like the Deploy Packager backup and restore processes. For more information, see Packager backup and restore process for SQL Server and Azure SQL Managed Instance.

Client Certificate Authentication

Client Certificate Authentication requires the Azure SQL Managed Instance information and the service principal attribute – Client ID. To use a certificate authentication mechanism, you also need:

X509 certificates.
A private key in a pks8 format. You should decrypt it and use it without a password.
Environment variables:
- DDB_AZURE_CLIENT_CERT is an absolute path to the client certificate.
- DDB_AZURE_CERT_KEY is an absolute path to the private key for the client certificate.

Creating a Service Principal

An Azure service principal is an identity created for use with applications, hosted services, and automated tools to access Azure resources. The roles assigned to the service principal have control over which resources can be accessed and at which level. For more information, see Create an Azure service principal with the Azure CLI or Create a service principal using the Azure portal.

You can create a service principal through the Azure portal or CLI. You must have an Owner role and specify it along with the resource group.

az login

az ad sp create-for-rbac -n "service_principal_name" --role Owner --scopes 
/subscriptions/a365ee0452e-67hr-497d/resourceGroups/dev

Changing "service_principal_name" to a valid URI of 
"http://service_principal_name", which is the required format used for 
service principal names

Creating 'Owner' role assignment under scope '/subscriptions/a365ee0452e-67hr-497d/resourceGroups/dev'

The output includes credentials that you must protect. Be sure that you do not include
these credentials in your code or check the credentials into your source control. 
For more information, see https://aka.ms/azadsp-cli

{

  "appId": "et78acie-some-id",

  "displayName": "service_principal_MI",

  "name": "http://service_principal_MI",

  "password": "bth6-some-password",

  "tenant": "fd564a-some-tenant"

}

Once a service principal is successfully created, ensure the appId (Client ID) attribute is available.

Creating a Client Certificate and Private Key Using the OpenSSL Utility

To create an X509 certificate and private key, follow these steps:

In your OpenSSL, run the command by replacing values from the example with the values you use:

The command will create the azure_test_certificate.crt certificate file and azure_test_pk.key private key file without a password.

Change the format of a private key file from PEM to pks8 and decrypt it by running the following command and using your values:

The command will create azure_test_pk_decrypted.key, which you can use for Azure Active Directory Client Certificate Authentication in Datical DB.

Uploading a Client Certificate to Azure App Registrations

Log in to your Microsoft Azure account.
Go to Azure Active Directory and select App registrations on the left side of the page.
Select All applications and choose an app that you want to use for the Azure SQL Managed Instance deployment.
On the left side of the page, select Certificates & secrets.
Select Upload certificate and upload the generated certificate.

Once you have the certificate and private key, you can use Azure Active Directory Client Certificate Authentication and run Deploy Packager.

Using Client Certificate Authentication

To use Azure Active Directory Client Certificate Authentication in the CLI, you need to:

Set the following environment variables on the system using certificate authentication with Datical DB:
- DDB_AZURE_CLIENT_CERT=absolute/path/to/the/client/certificate
- DDB_AZURE_CERT_KEY=absolute/path/to/the/private/key/for/the/client/certificate
Run the newDbDef command with the Azure REST API authentication option called azureRestAuthenticationOption and the certificate value. The newDbDef command creates a new dbDef based on the key and value pairs specified as arguments:

There is no need to submit a username and password when you use Azure Active Directory Authentication.

After running the newDbDef command with the certificate value, you will see azureRestAuthenticationOption=certificate in your datical.project file.