如何在Azure Pipelines中通过JDBC实现基于服务主体的无密码认证？

环境信息 #

• CI/CD：Azure Pipelines
• 操作系统：Ubuntu（最新版）
• Java：OpenJDK 11
• 数据库：Azure SQL
• 数据库迁移工具：Flyway（v6.0.1）

问题描述 #

希望在Azure环境中用服务主体替代密码进行身份验证，在Azure Pipelines运行Flyway数据库迁移脚本时，尝试通过ADAL4J实现ActiveDirectoryIntegrated身份验证，但始终加载失败。

简化后的Pipeline脚本 #

variables:
  FLYWAY_VERSION: '6.0.1'

pool:
  vmImage: "ubuntu-latest"

steps:
  - 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

执行错误信息 #

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.

已尝试的解决方案 #

1. 手动添加ADAL4J到Flyway的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. 

2. 切换到Windows虚拟机并使用PowerShell：

pool:
  vmImage: "windows-latest"

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

解决方法 #

方案1：补充ADAL4J的完整依赖包 #

ADAL4J本身依赖多个第三方库，仅添加adal4j.jar无法满足运行要求，需要下载所有关联依赖放到Flyway的jars目录：

# 创建临时目录存放依赖
mkdir temp-jars
cd temp-jars

# 批量下载必要依赖
curl -O https://repo1.maven.org/maven2/com/microsoft/azure/adal4j/1.6.6/adal4j-1.6.6.jar
curl -O https://repo1.maven.org/maven2/javax/activation/activation/1.1.1/activation-1.1.1.jar
curl -O https://repo1.maven.org/maven2/commons-codec/commons-codec/1.11/commons-codec-1.11.jar
curl -O https://repo1.maven.org/maven2/commons-logging/commons-logging/1.2/commons-logging-1.2.jar
curl -O https://repo1.maven.org/maven2/com/google/guava/guava/25.1-jre/guava-25.1-jre.jar
curl -O https://repo1.maven.org/maven2/com/sun/mail/javax.mail/1.5.6/javax.mail-1.5.6.jar
curl -O https://repo1.maven.org/maven2/net/minidev/json-smart/2.3/json-smart-2.3.jar
curl -O https://repo1.maven.org/maven2/com/nimbusds/nimbus-jose-jwt/8.19/nimbus-jose-jwt-8.19.jar
curl -O https://repo1.maven.org/maven2/org/slf4j/slf4j-api/1.7.25/slf4j-api-1.7.25.jar

# 移动到Flyway jars目录
mv *.jar ../flyway-$(FLYWAY_VERSION)/jars/
cd ..
rm -rf temp-jars

方案2：改用ActiveDirectoryServicePrincipal认证模式 #

直接使用服务主体专属的认证模式，无需依赖ADAL4J，利用Azure Pipeline中已有的服务主体凭据：

# 从AzureCLI上下文提取服务主体信息
SP_ID=$(az account show --query user.name -o tsv)
SP_SECRET=$(az account get-access-token --query accessToken -o tsv)
TENANT_ID=$(az account show --query tenantId -o tsv)

# 执行Flyway迁移
./flyway-$(FLYWAY_VERSION)/flyway \
  -locations="filesystem:./DataChanges/test" \
  -url="jdbc:sqlserver://***;Authentication=ActiveDirectoryServicePrincipal;databaseName=你的数据库名" \
  -user="$SP_ID" \
  -password="$SP_SECRET" \
  migrate -X

方案3：升级Flyway版本 #

Flyway 6.0.1版本较陈旧，后续v7及以上版本对Azure SQL的AD认证支持更完善，内置了必要的依赖库，升级后可直接使用ActiveDirectoryIntegrated模式，无需手动添加jar包。

内容的提问来源于stack exchange，提问作者Atli