Summary

This discussion proposes adding Microsoft Entra ID (Azure AD) ActiveDirectoryServicePrincipal authentication as a non-breaking option for the MSSQL source connector, leveraging the JDBC driver's native support.

Related Issues

• #9557 — "[source-MSSQL] Support Windows authentication" (22+ upvotes, frozen)
• #20866 — "Source MSSQL - Failed to load MSAL4J Java library for performing ActiveDirectoryPassword authentication" (frozen)
• Community PR #61547 — Previous community attempt (closed during CDK migration)

Prior Art (Closed PR)

A reference implementation was developed in PR #74904 (now closed) on branch devin/1773700827-mssql-entra-id-auth. The code changes are preserved there for reference.

Motivation

Multiple users across issues #9557 and #20866 have stated that the lack of Entra ID / non-SQL-password authentication is a dealbreaker for their organizations:

"Lack of this feature disqualifies Airbyte as a candidate for our organization (big Danish governmental organization)."

"For organizations heavy on MSSQL, it makes the entire Airbyte software kind of a non-starter."

Many enterprises require Entra ID authentication for Azure SQL Database / Azure Synapse Analytics and cannot use SQL Server authentication for security compliance reasons.

Proposed Approach

Leverage JDBC Driver's Native Support

The MSSQL JDBC driver (mssql-jdbc:12.10.1) already supports Entra ID authentication natively via the authentication connection property. The azure-identity:1.15.3 dependency is already present in the connector's build.gradle. No new dependencies are needed.

Non-Breaking Design

Add an optional authentication oneOf selector to the connector spec (following the existing EncryptionSpecification sealed interface pattern):

• sql_password (default) — username + password (current behavior, fully backward compatible)
• active_directory_service_principal (new) — tenant_id + client_id + client_secret

When no authentication block is present in the config, the connector falls back to top-level username/password fields — zero disruption to existing connections.

JDBC Property Mapping

Implementation Details

The reference implementation (branch devin/1773700827-mssql-entra-id-auth) touches three files:

1. MsSqlServerSourceConfigurationSpecification.kt

   • Makes username/password fields nullable (from lateinit var to var String? = null)
   • Adds AuthenticationSpecification sealed interface with SqlPasswordAuthentication and ActiveDirectoryServicePrincipalAuthentication variants
   • Adds @JsonSetter/@JsonGetter methods for the optional authentication block
   • Adds MicronautPropertiesFriendlyAuthenticationSpecification for Micronaut DI compatibility

2. MsSqlServerSourceConfiguration.kt

   • Updates the factory to check for authenticationJson and set JDBC properties accordingly
   • Falls back to top-level username/password when no auth block is present

3. MsSqlServerSourceConfigurationSpecificationTest.kt

   • Adds tests for backward compatibility, SQL password auth block, and service principal auth block

Why Not OAuth/Consent Flow?

The Azure Blob Storage connector uses a platform-level OAuth consent flow (MicrosoftAzureBlobStorageOAuthFlow.kt in airbyte-platform-internal). This approach was evaluated but is not recommended for MSSQL because:

• The JDBC driver handles token acquisition internally — manually managing tokens would fight the driver's architecture
• It would require changes in both the connector repo AND airbyte-platform-internal
• Service Principal auth is the enterprise standard for machine-to-machine workloads like data pipelines
• No other JDBC-based Airbyte connector uses a platform-side OAuth consent flow

Future Extensions

Phase 2 could add:

• ActiveDirectoryDefault — uses DefaultAzureCredential (managed identity for Azure-hosted Airbyte)
• ActiveDirectoryPassword — Entra username + password (deprecated by Microsoft but still supported)
• User-delegated OAuth consent flow (if demand justifies the cross-repo complexity)

Open Questions

1. Version bump: Should this be a patch bump (4.3.6) or minor bump (4.4.0)? The change is additive and non-breaking.
2. SpotBugs: The reference implementation triggered SpotBugs warnings due to nullable username/password fields. Need to add appropriate @SuppressFBWarnings annotations or restructure.
3. JSON schema ordering: The authentication field uses "order":4, which may collide with the existing username field. May need adjustment.
4. Documentation: What level of docs changes are needed? (connector docs, migration guide, etc.)

Devin session: https://app.devin.ai/sessions/a499a6f3e5b94104955a35bc765954a9
Requested by: kevin.gavino