Support for more personal authentication modes for Azure SQL

[audunsolemdal]

Follow up to #1611 (comment) and #1753

First of all thank you for supporting Entra auth in v4. This will save my org time as we plan to migrate a lot of different databases and do not want to deal with setting up SQL username + password auth for all of them when we have a entra setup working already.

First let me clarify that I am familiar with the different authentication types mentioned here, but I am mostly familiar with the Azure.Identity package in .NET, so some details may be different in MSAL4J.

Currently the only personal auth method is ActiveDirectoryPassword

Two issues with supporting this:

1. It is deprecated due to MFA requirements https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver17#connect-using-activedirectorypassword-authentication-mode

The ActiveDirectoryPassword authentication option (Microsoft Entra ID password authentication) is deprecated in the Microsoft SQL drivers. This high-risk authentication flow is incompatible with [mandatory Microsoft Entra multifactor authentication (MFA)](https://learn.microsoft.com/en-us/entra/identity/authentication/concept-mandatory-multifactor-authentication) and might not work in tenants where MFA is enforced. Plan to migrate to a different Microsoft Entra authentication method.

2. If I am not mistaken you can't really get this working anymore unless you create some exceptions to conditional access (not recommended), re: https://learn.microsoft.com/en-us/entra/identity/authentication/concept-mandatory-multifactor-authentication?tabs=dotnet

For instance I just tried the ActiveDirectoryPassword auth and ended up with this:

[1/3] Running pgloader...
10:02:38.167 INFO  [main] pgloader.cli - Parsing commands from file /tmp/tmp.chCNaCp5bl/migration.load
10:02:38.772 INFO  [main] pgloader.core - Connected to PostgreSQL at postgresql://nis-gateway:kZ_NHmzwgVCus2tsL6TMn16BS_Dlf07X0L1vhF03ubg@127.0.0.1:5432/gateway?sslmode=disable
10:02:40.137 WARN  [pool-2-thread-1] c.m.a.m.PublicClientApplication - [Correlation ID: 7f27db7b-25f8-4428-929b-743fa7f9987b] Execution of class com.microsoft.aad.msal4j.AcquireTokenByAuthorizationGrantSupplier failed: AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access xxxx-aaa-bbbb'. Trace ID: 5a2948ef-bc6d-4b8e-bbfa-be5fb76c5700 Correlation ID: 7f27db7b-25f8-4428-929b-743fa7f9987b Timestamp: 2026-07-06 08:02:38Z

Essentially what my org is used to using is Active Directory Default when connecting to a database from localhost. During local development I recommened our developers to run az login before starting their applications to connect to the Azure SQL databases. I think the same would make sense here. Currently when setting the connection string I end up with:

[1/3] Running pgloader...
10:00:12.006 INFO  [main] pgloader.cli - Parsing commands from file /tmp/tmp.iPqkUaOklD/migration.load
10:00:12.619 INFO  [main] pgloader.core - Connected to PostgreSQL at postgresql://nis-gateway:kZ_NHmzwgVCus2tsL6TMn16BS_Dlf07X0L1vhF03ubg@127.0.0.1:5432/gateway?sslmode=disable
Exception in thread "main" java.lang.NoClassDefFoundError: com/azure/identity/DefaultAzureCredentialBuilder

Before you created #1753 Claude did manage to generate working code for me to authenticate with Active Directory Default via Azure CLI, which shows that it should be possible. The generated code was not very clean so I don't have a PR to contribute with unfortunately.

[dimitri]

@audunsolemdal Thanks for the detailed write-up — and for linking the workaround in miljodir/DataEngineerPersona#6. That PR turned out to be exactly the right pointer: it confirmed the correct azure-identity version to pin (1.11.1, the same one mssql-jdbc 12.6.1 was certified against), documented the WSL2 linux_xdg_open_failed failure for ActiveDirectoryInteractive that I'd only inferred from the driver source, and gave a real-world test of the ActiveDirectoryDefault + az login connection string. All of that fed directly into the fix.

PR #1755 (now merged into main) bundles com.azure/azure-identity 1.11.1 in the pgloader v4 uberjar, which should make the following modes work out of the box:

• ActiveDirectoryDefault — the az login fallback chain, including AzureCliCredential in WSL2
• ActiveDirectoryAzCli — explicit CLI token
• ActiveDirectoryWorkloadIdentity — for Kubernetes OIDC

The connection string from your workaround should work directly against a fresh JAR build:

FROM "jdbc:sqlserver://$SQLSERVER_HOST:$SQLSERVER_PORT;databaseName=$SQLSERVER_DB;authentication=ActiveDirectoryDefault;encrypt=true;trustServerCertificate=false"

The dev release JAR is rebuilt automatically on every merge to main:

curl -L -o pgloader.jar \
    https://github.com/dimitri/pgloader/releases/download/v4-dev/pgloader.jar
java -jar pgloader.jar --version

Would you be able to test this against your Azure SQL setup and let us know if it fully resolves the issue? In particular: does ActiveDirectoryDefault pick up your az login session as expected, and are there any edge cases with your tenant's conditional access or Entra configuration that still cause problems?

[audunsolemdal]

Would you be able to test this against your Azure SQL setup and let us know if it fully resolves the issue? In particular: does ActiveDirectoryDefault pick up your az login session as expected, and are there any edge cases with your tenant's conditional access or Entra configuration that still cause problems?

ActiveDirectoryDefault works without issues and picks up my az login.
My only complaint here is that the output is very spammy. Essentially it seems that multiple tokens are fetched and every time this happens the ChainedTokenCredential logs appear like this:

14:06:37.618 INFO  [main] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential EnvironmentCredential is unavailable.
14:06:37.619 INFO  [main] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential WorkloadIdentityCredential is unavailable.
14:06:38.206 WARN  [ForkJoinPool.commonPool-worker-1] c.m.a.m.ConfidentialClientApplication - [Correlation ID: 11804000-9ee2-41f5-8358-5d36a6d5af71] Execution of class com.microsoft.aad.msal4j.AcquireTokenByClientCredentialSupplier failed: java.util.concurrent.ExecutionException: com.azure.identity.CredentialUnavailableException: ManagedIdentityCredential authentication unavailable. Connection to IMDS endpoint cannot be established, Connect timed out.
14:06:38.206 INFO  [ForkJoinPool.commonPool-worker-1] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential ManagedIdentityCredential is unavailable.
14:06:38.209 ERROR [ForkJoinPool.commonPool-worker-1] c.a.i.i.IdentityClient - Could not load the brokered authentication library. Ensure that the azure-identity-broker library is on the classpath.
14:06:38.210 INFO  [ForkJoinPool.commonPool-worker-1] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential SharedTokenCacheCredential is unavailable.
14:06:38.259 INFO  [ForkJoinPool.commonPool-worker-1] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential IntelliJCredential is unavailable.
14:06:39.066 INFO  [ForkJoinPool.commonPool-worker-1] c.a.i.AzureCliCredential - Azure Identity => getToken() result for scopes [https://database.windows.net//.default]: SUCCESS
14:06:39.066 INFO  [ForkJoinPool.commonPool-worker-1] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential AzureCliCredential returns a token
....
14:06:39.132 INFO  [main] p.source.mssql - Fetching table list from MS SQL Server
...
14:17:07.504 INFO  [main] pgloader.core - Preparing target PostgreSQL schema
14:17:07.627 INFO  [pool-4-thread-2] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential EnvironmentCredential is unavailable.
14:17:07.628 INFO  [pool-4-thread-2] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential WorkloadIdentityCredential is unavailable.
14:17:07.631 INFO  [pool-4-thread-4] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential EnvironmentCredential is unavailable.
14:17:07.631 INFO  [pool-4-thread-1] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential EnvironmentCredential is unavailable.
14:17:07.631 INFO  [pool-4-thread-1] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential WorkloadIdentityCredential is unavailable.
14:17:07.631 INFO  [pool-4-thread-4] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential WorkloadIdentityCredential is unavailable.
14:17:07.641 INFO  [pool-4-thread-3] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential EnvironmentCredential is unavailable.
14:17:07.642 INFO  [pool-4-thread-3] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential WorkloadIdentityCredential is unavailable.
14:17:08.131 WARN  [ForkJoinPool.commonPool-worker-1] c.m.a.m.ConfidentialClientApplication - [Correlation ID: efaa7a78-be89-4c77-a6a4-440b2e6d2f40] Execution of class com.microsoft.aad.msal4j.AcquireTokenByClientCredentialSupplier failed: java.util.concurrent.ExecutionException: com.azure.identity.CredentialUnavailableException: ManagedIdentityCredential authentication unavailable. Connection to IMDS endpoint cannot be established, Connect timed out.
14:17:08.131 INFO  [ForkJoinPool.commonPool-worker-1] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential ManagedIdentityCredential is unavailable.
14:17:08.132 ERROR [ForkJoinPool.commonPool-worker-1] c.a.i.i.IdentityClient - Could not load the brokered authentication library. Ensure that the azure-identity-broker library is on the classpath.
14:17:08.132 INFO  [ForkJoinPool.commonPool-worker-1] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential SharedTokenCacheCredential is unavailable.
14:17:08.133 INFO  [ForkJoinPool.commonPool-worker-1] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential IntelliJCredential is unavailable.
14:17:08.134 WARN  [ForkJoinPool.commonPool-worker-2] c.m.a.m.ConfidentialClientApplication - [Correlation ID: bce3e8e9-9e95-47cf-a04b-dd90d5ce74b1] Execution of class com.microsoft.aad.msal4j.AcquireTokenByClientCredentialSupplier failed: java.util.concurrent.ExecutionException: com.azure.identity.CredentialUnavailableException: ManagedIdentityCredential authentication unavailable. Connection to IMDS endpoint cannot be established, Connect timed out.
14:17:08.134 WARN  [ForkJoinPool.commonPool-worker-3] c.m.a.m.ConfidentialClientApplication - [Correlation ID: 9d1406a4-a3a4-4709-9dfe-d10275652b18] Execution of class com.microsoft.aad.msal4j.AcquireTokenByClientCredentialSupplier failed: java.util.concurrent.ExecutionException: com.azure.identity.CredentialUnavailableException: ManagedIdentityCredential authentication unavailable. Connection to IMDS endpoint cannot be established, Connect timed out.
14:17:08.134 INFO  [ForkJoinPool.commonPool-worker-2] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential ManagedIdentityCredential is unavailable.
14:17:08.134 INFO  [ForkJoinPool.commonPool-worker-3] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential ManagedIdentityCredential is unavailable.
14:17:08.134 ERROR [ForkJoinPool.commonPool-worker-3] c.a.i.i.IdentityClient - Could not load the brokered authentication library. Ensure that the azure-identity-broker library is on the classpath.
14:17:08.134 ERROR [ForkJoinPool.commonPool-worker-2] c.a.i.i.IdentityClient - Could not load the brokered authentication library. Ensure that the azure-identity-broker library is on the classpath.
14:17:08.135 INFO  [ForkJoinPool.commonPool-worker-3] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential SharedTokenCacheCredential is unavailable.
14:17:08.135 INFO  [ForkJoinPool.commonPool-worker-2] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential SharedTokenCacheCredential is unavailable.
14:17:08.136 INFO  [ForkJoinPool.commonPool-worker-2] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential IntelliJCredential is unavailable.
14:17:08.136 INFO  [ForkJoinPool.commonPool-worker-3] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential IntelliJCredential is unavailable.
14:17:08.144 WARN  [ForkJoinPool.commonPool-worker-4] c.m.a.m.ConfidentialClientApplication - [Correlation ID: 1c3ecabd-b6b9-4ae1-a9ed-a554bfd7172a] Execution of class com.microsoft.aad.msal4j.AcquireTokenByClientCredentialSupplier failed: java.util.concurrent.ExecutionException: com.azure.identity.CredentialUnavailableException: ManagedIdentityCredential authentication unavailable. Connection to IMDS endpoint cannot be established, Connect timed out.
14:17:08.144 INFO  [ForkJoinPool.commonPool-worker-4] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential ManagedIdentityCredential is unavailable.
14:17:08.145 ERROR [ForkJoinPool.commonPool-worker-4] c.a.i.i.IdentityClient - Could not load the brokered authentication library. Ensure that the azure-identity-broker library is on the classpath.
14:17:08.145 INFO  [ForkJoinPool.commonPool-worker-4] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential SharedTokenCacheCredential is unavailable.
14:17:08.147 INFO  [ForkJoinPool.commonPool-worker-4] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential IntelliJCredential is unavailable.
14:17:09.117 INFO  [ForkJoinPool.commonPool-worker-1] c.a.i.AzureCliCredential - Azure Identity => getToken() result for scopes [https://database.windows.net//.default]: SUCCESS
14:17:09.118 INFO  [ForkJoinPool.commonPool-worker-1] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential AzureCliCredential returns a token
14:17:09.132 INFO  [ForkJoinPool.commonPool-worker-2] c.a.i.AzureCliCredential - Azure Identity => getToken() result for scopes [https://database.windows.net//.default]: SUCCESS
14:17:09.132 INFO  [ForkJoinPool.commonPool-worker-2] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential AzureCliCredential returns a token
14:17:09.137 INFO  [ForkJoinPool.commonPool-worker-3] c.a.i.AzureCliCredential - Azure Identity => getToken() result for scopes [https://database.windows.net//.default]: SUCCESS
14:17:09.137 INFO  [ForkJoinPool.commonPool-worker-3] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential AzureCliCredential returns a token
14:17:09.158 INFO  [ForkJoinPool.commonPool-worker-4] c.a.i.AzureCliCredential - Azure Identity => getToken() result for scopes [https://database.windows.net//.default]: SUCCESS
14:17:09.159 INFO  [ForkJoinPool.commonPool-worker-4] c.a.i.ChainedTokenCredential - Azure Identity => Attempted credential AzureCliCredential returns a token

I have not seen authentication=ActiveDirectoryAzCli before. And I get the error below. Perhaps this is only supported in Golang, ref. the results here: https://github.com/search?q=ActiveDirectoryAzCli&type=code&p=1

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The authentication value "ActiveDirectoryAzCli" is not valid.
        at com.microsoft.sqlserver.jdbc.SqlAuthentication.valueOfString(SQLServerDriver.java:121)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2724)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1953)
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1263)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:191)
        at pgloader.source.mssql$connection.invokeStatic(mssql.clj:113)
        at pgloader.source.mssql$connection.invoke(mssql.clj:102)
        at pgloader.source.mssql$create_source.invokeStatic(mssql.clj:355)
        at pgloader.source.mssql$create_source.invoke(mssql.clj:353)
        at pgloader.core$source_from_uri.invokeStatic(core.clj:124)
        at pgloader.core$source_from_uri.doInvoke(core.clj:101)
        at clojure.lang.RestFn.invoke(RestFn.java:500)
        at pgloader.core$run_command.invokeStatic(core.clj:441)
        at pgloader.core$run_command.invoke(core.clj:411)
        at pgloader.cli$run.invokeStatic(cli.clj:351)
        at pgloader.cli$run.invoke(cli.clj:327)
        at pgloader.cli$_main.invokeStatic(cli.clj:365)
        at pgloader.cli$_main.doInvoke(cli.clj:362)
        at clojure.lang.RestFn.applyTo(RestFn.java:140)
        at pgloader.cli.main(Unknown Source)

[dimitri]

Thank you — this is exactly the kind of real-world feedback that makes the difference between a feature that looks correct and one that is correct.

Two follow-up fixes landed in PR #1756:

ActiveDirectoryAzCli and ActiveDirectoryWorkloadIdentity removed from the docs

These modes don't exist in the Java mssql-jdbc driver — they're Microsoft.Data.SqlClient (.NET) and go-mssqldb (Go) concepts only. We added them by mistake when reading azure-identity's own documentation rather than checking mssql-jdbc's SqlAuthentication enum directly. Verified via javap: the valid mssql-jdbc 12.6.1 modes are ActiveDirectoryDefault, ActiveDirectoryServicePrincipal, ActiveDirectoryServicePrincipalCertificate, ActiveDirectoryManagedIdentity, ActiveDirectoryIntegrated, ActiveDirectoryInteractive, ActiveDirectoryPassword.

Verbose DefaultAzureCredential chain logging suppressed

The INFO-level messages for each unavailable credential (EnvironmentCredential unavailable, ManagedIdentityCredential unavailable, etc.) are expected behaviour when running on a developer machine, but very noisy — and multiplied by pgloader's connection pool thread count. We've set com.azure.identity to WARN and com.microsoft.aad.msal4j to ERROR in logback.xml so those chain traversal messages are suppressed by default while genuine errors still surface.

---

On the lack of CI testing for this

We don't currently have end-to-end CI coverage for Azure AD authentication because it genuinely requires real Azure infrastructure — SQL Server in Docker has no Entra ID integration, so it can't be mocked. The right approach would be an Azure SQL Database serverless instance (free tier) with GitHub Actions OIDC / Workload Identity Federation so no secrets need to be stored. If you or anyone at your org would be willing to contribute that Azure setup, we'd be happy to wire it into the CI workflow — the pgloader side is straightforward once the Azure SQL instance and app registration exist.

[audunsolemdal]

The INFO-level messages for each unavailable credential (EnvironmentCredential unavailable, ManagedIdentityCredential unavailable, etc.) are expected behaviour when running on a developer machine, but very noisy — and multiplied by pgloader's connection pool thread count. We've set com.azure.identity to WARN and com.microsoft.aad.msal4j to ERROR in logback.xml so those chain traversal messages are suppressed by default while genuine errors still surface.

Works well, thanks.