How can I use Passwordless authentication via JDBC through Azure Pipelines?

Question

My goal is to use Service Principals for authentications instead of passwords in my Azure environment.

In my Azure Pipelines I have a database migration script that I'm struggling to get working with ActiveDirectoryIntegrated authentication via ADAL4J.

Environment:

• CI/CD: Azure Pipelines
• OS: Ubuntu (latest)
• Java: OpenJDK 11
• Database: Azure SQL
• Database Migration Tool: Flyway (v6.0.1)

My simplified script showcases what I want to achieve.

variables:
  FLYWAY_VERSION: '6.0.1'

pool:
  vmImage: "ubuntu-latest"

  - task: AzureCLI@2
    inputs:
      azureSubscription: 'MyServicePrincipalSubscription'
      scriptType: 'bash'
      scriptLocation: 'inlineScript'
      inlineScript: |

        # Install Flyway
        curl -L https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/$(FLYWAY_VERSION)/flyway-commandline-$(FLYWAY_VERSION)-linux-x64.tar.gz -o flyway.tar.gz
        tar -xzf flyway.tar.gz

        # Run migrations
        ./flyway-$(FLYWAY_VERSION)/flyway \
          -locations="filesystem:./DataChanges/test" \
          -url="jdbc:sqlserver://***;Authentication=ActiveDirectoryIntegrated" \
          migrate -X

This results in an error loading ADAL4J, no matter what I try.

SQL State  : null
Error Code : 0
Message    : Failed to load both sqljdbc_auth.dll and ADAL4J Java library for performing ActiveDirectoryIntegrated authentication. Please install one of them to proceed.

I have tried the following

• Adding ADAL4J manually to JARS

curl -L https://repo1.maven.org/maven2/com/microsoft/azure/adal4j/1.6.6/adal4j-1.6.6.jar -o adal4j.jar

mv adal4j.jar flyway-$(FLYWAY_VERSION)/jars/

DEBUG: Adding location to classpath: /home/vsts/work/1/s/flyway-6.0.1/jars/adal4j.jar <-- Confirmation of loading. 

• Changing to Windows VM and using Powershell

pool:
  vmImage: "windows-latest"


  - task: AzureCLI@2
    inputs:
      azureSubscription: 'MyServicePrincipalSubscription'
      scriptType: 'ps'

Answer 1

As advised by Ziyang Liu-MSFT, my approach was not correct. What worked for me and seems to be the standard is the following.

Use ActiveDirectoryServicePrincipal along with Client ID/Secret in the connection string.

Flyway v6 JDBC does not include support for ActiveDirectoryServicePrincipal, therefore an upgrade is required as well.

variables:
  FLYWAY_VERSION: '8.5.13'

pool:
  vmImage: "ubuntu-latest"

steps:
  - script: |

      # Install Flyway
      curl -L https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/$(FLYWAY_VERSION)/flyway-commandline-$(FLYWAY_VERSION)-linux-x64.tar.gz -o flyway.tar.gz
      tar -xzf flyway.tar.gz

      # Run migrations
      ./flyway-$(FLYWAY_VERSION)/flyway \
        -locations="filesystem:./DataChanges/test" \
        -url="jdbc:sqlserver://***;user=$(CLIENT_ID);password=$(CLIENT_SECRET);Authentication=ActiveDirectoryServicePrincipal" \
        migrate -X