How To: Troubleshoot Azure SQL Managed Instance and SQL Database connections with MSI Authentication

Below are documentation, Azure network prerequisites, configurations, and potentials errors when setting up Azure SQL Managed Instances and Azure SQL databases with Azure Managed Identity (ActiveDirectoryMSI) authentication. This is also called Active Directory Managed Identity (ActiveDirectoryManagedIdentity).

Documentation

Please review the Liquibase Enterprise documentation for Creating Connections in Liquibase Enterprise and Azure SQL Managed Instance and Azure SQL Managed Instance Setup.

Prerequisites

The following items are required to use Azure’s SQL Managed Instance with MSI Authentication.

• SQL Managed Instance (MI) database

• Virtual machine (VM) or client in the same or peered Virtual Network (VNet) as the database

• User-Assigned Managed Identity

• Admin Security Group

• Liquibase installation on the VM along with installed drivers

Azure Configurations

1. Ensure the Managed Identity exists in the Azure Portal. This can be found in Azure under Managed Identities.

   Open

   

   1. The Client ID on the Managed Identity will be used as the MSI Client ID on the Liquibase DbDef.

   2. Open

      

      Azure Managed Identity Client ID
   3. Open

      

      Liquibase Enterprise dbDef

       

2. Ensure the Managed Identity is assigned to your Virtual Machine as a User Assigned Identity.

   1. Note: Do not use the System assigned Identity as the MSI Client ID.

   2. In Azure Portal, go to Virtual machine → Security → Identity and select the User assigned tab

      Open

      

3. Ensure the SQL Managed Instance or SQL server has a Microsoft Entra admin under Settings → Microsoft Entra ID

   Open

   

4. Ensure the Managed Identity from Step 1 is a member of the Admin group specified in Step 3. This can be found in Azure under Groups → <Admin group> → Manage → Members.

   Open

   

5. Ensure the Managed Identity is added to the SQL Managed Instance or SQL server. In Azure Portal, check under SQL servers → <SQL server> → Security → Identity under User assigned managed identity.

   Open

   

Additional Checks

1. Check connectivity to the database from the VM by using tools such as telnet, nc, sqlcmd, or az network watcher.

2. Check that Instance Name has been left blank in the Liquibase Enterprise dbDef. (Although technically Liquibase Enterprise will ignore this value.)

3. Check that drivers have been installed correctly.

   1. GUI - Go to Help → Check for Updates

      Open

      

   2. CLI - Run hammer checkdrivers

Potential Errors

The Test Connection may return the following errors:

1. Connection could not be created to <jdbc_url> due to Login fail for user ‘<token-identified principal>’. ClientConnectionId: xxxx

   1. Suggestions: This likely means the Client ID is valid, but the required permissions are not present.

      1. Check the Managed Identity is a member of the admin group (see Step 4 above.)

      2. Check the Managed Identity is present on the SQL server (see Step 5 above.)

2. Connection could not be created to <jdbc_url> due to MSI Token failure: Failed to acquire access token from IMDS, verify your clientId.

   1. Suggestions: This generally means the Client ID is not valid.

      1. Check that you have provided the correct value for the Client ID string and that you are not missing any characters, etc.

      2. Check that you have provided the User Assigned Identity versus the System assigned Identity (see Step 2 above.)

3. Connection could not be created to <jdbc_url> due to Database ‘<database>’ on server ‘<hostname>’ is not currently available. Please retry the connection later.

   1. Serverless MI databases go off-line based on usage. A connection attempt should restart the database so retry the connection after a minute or so.