feat(source-mssql): Add Microsoft Entra ID Service Principal Authentication Support

[devin-ai-integration[bot]]

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

Auth Method	JDBC Properties Set
SQL Password (current)	user = username, password = password
Service Principal (new)	user = client_id, password = client_secret, authentication = ActiveDirectoryServicePrincipal, tenantId = tenant_id

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

[Aaron ("AJ") Steers (aaronsteers)]

Hi, thank you for opening this detailed feature request!

This is a well-documented proposal for adding Microsoft Entra ID Service Principal authentication to the MSSQL source connector. The motivation is clear — multiple enterprise users have identified the lack of Entra ID authentication as a blocker for adopting Airbyte with Azure SQL Database and Azure Synapse Analytics.

A few highlights that make this a strong request:

• The MSSQL JDBC driver already natively supports ActiveDirectoryServicePrincipal authentication
• The azure-identity dependency is already present in the connector — no new dependencies needed
• The proposed design is non-breaking and follows existing patterns in the codebase
• A reference implementation already exists on branch devin/1773700827-mssql-entra-id-auth (PR #74904)

This has been escalated to the internal team for evaluation: https://github.com/airbytehq/oncall/issues/11673

Regarding the open questions raised in the discussion:

1. Version bump: Since this is additive and non-breaking, a minor bump (4.4.0) would be appropriate per semantic versioning conventions.
2. SpotBugs: Adding @SuppressFBWarnings annotations to the nullable fields should resolve the warnings.
3. JSON schema ordering: This should be adjusted to avoid collisions with existing fields.
4. Documentation: At minimum, the connector docs page should be updated to document the new authentication option.

---

Need more help? Join Airbyte Community Slack for peer support, or if you're a Cloud customer, open a support ticket referencing this URL.