Page Comparison

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 Managed 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 SqlServer 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

• Instance Name

Additionally, ensure that you selected the Azure SQL Managed Instance connection type and entered the needed instance name.

...

Azure REST API Settings

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

...

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 to 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. This access is restricted by the The roles assigned to the service principal , giving you 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. For this, you 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:

...

This configuration focuses on a single-tenant application where the application is intended to 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

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

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:

openssl req -x509 -days 365 -nodes -newkey rsa:4096 -keyout azure_test_pk.key 
-out azure_test_certificate.crt -subj "/C=USA/ST=Texas/L=Austin/CN=www.liquibase.com/emailAddress=example@liquibase.com"

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:

openssl pkcs8 -topk8 -inform PEM -outform DER -in azure_test_pk.key 
-out azure_test_pk_decrypted.key -nocrypt

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

1. Log in to your Microsoft Azure account.

2. Go to Azure Active Directory and select App registrations on the left side of the page.

3. Select All applications and choose an app that you want to use for the Azure SQL Managed Instance deployment.

4. On the left side of the page, select Certificates & secrets.

5. 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 

hammer newDbDef DbDefClass SqlServerDbDef dbType AZ_SQLMI 
azureRestAuthenticationOption certificate 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