mssql: delegate TokenCredential authentication to mssql-python

[sdebruyn]

Context

The current credential path eagerly calls azure_credential.get_token(), manually builds the ODBC access-token struct, and passes it through attrs_before:

• dlt/dlt/destinations/impl/mssql/configuration.py

  Lines 15 to 56 in a831096

  	# ODBC attribute used to inject a pre-acquired Entra ID access token, kept for a future explicit
  	# access-token feature. Not used by any authentication method today.
  	# https://learn.microsoft.com/sql/connect/odbc/using-azure-active-directory#authenticating-with-an-access-token
  	SQL_COPT_SS_ACCESS_TOKEN = 1256
  	SQL_TOKEN_SCOPE = "https://database.windows.net/.default"
  	# Entra ID authentication methods supported by mssql-python's `Authentication=` connection
  	# option. mssql-python performs the sign-in for all of them; dlt only builds the DSN.
  	SUPPORTED_AUTHENTICATION = frozenset(
  	{
  	"ActiveDirectoryServicePrincipal",
  	"ActiveDirectoryPassword",
  	"ActiveDirectoryIntegrated",
  	"ActiveDirectoryInteractive",
  	"ActiveDirectoryMsi",
  	"ActiveDirectoryDefault",
  	"ActiveDirectoryDeviceCode",
  	}
  	)
  	# Thin alias for `ActiveDirectoryDefault`, resolved by `_normalize_authentication`.
  	_AUTHENTICATION_ALIASES = {
  	"default": "ActiveDirectoryDefault",
  	}
  	def _normalize_authentication(authentication: str) -> str:
  	"""Resolve the thin `default` alias to the canonical `ActiveDirectoryDefault` name."""
  	return _AUTHENTICATION_ALIASES.get(authentication.lower(), authentication)
  	def build_token_attrs_before(credentials: Any) -> dict[int, bytes] | None:
  	"""Return `attrs_before` with a directly injected Entra ID access token, or None."""
  	if credentials.access_token:
  	token = str(credentials.access_token)
  	elif credentials.azure_credential:
  	token = credentials.azure_credential.get_token(SQL_TOKEN_SCOPE).token
  	else:
  	return None
  	encoded_token = token.encode("utf-16-le")
  	token_struct = struct.pack(f"<I{len(encoded_token)}s", len(encoded_token), encoded_token)
  	return {SQL_COPT_SS_ACCESS_TOKEN: token_struct}

• dlt/dlt/destinations/impl/mssql/configuration.py

  Lines 181 to 185 in a831096

  	access_token: Optional[TSecretStrValue] = None
  	"""Pre-acquired Entra ID access token, injected as-is. Takes precedence over `azure_credential` and `authentication`"""
  	azure_credential: Annotated[Optional[Any], NotResolved()] = None
  	"""A `TokenCredential` injected at runtime, e.g. `DefaultAzureCredential()`. Takes precedence over `authentication` but not over `access_token`"""

• dlt/dlt/destinations/impl/mssql/configuration.py

  Lines 242 to 248 in a831096

  	def to_odbc_dsn(self) -> str:
  	params = self.get_odbc_dsn_dict()
  	return build_odbc_dsn(params)
  	def to_odbc_attrs_before(self) -> dict[int, bytes] | None:
  	"""Return `attrs_before` with a directly injected Entra ID access token, or None."""
  	return build_token_attrs_before(self)

• dlt/dlt/destinations/impl/mssql/sql_client.py

  Lines 39 to 54 in a831096

  	def open_connection(self) -> mssql_python.Connection:
  	# mssql-python bundles its own driver, so the connection string carries no DRIVER, and it
  	# signs in for every supported Entra ID authentication method itself from the
  	# `Authentication=` DSN keyword — dlt injects no `attrs_before` for those.
  	#
  	# mssql-python auto-enables connection pooling (default: 100 connections, 600s idle
  	# timeout) on the first connection any process opens, unless the application calls
  	# `mssql_python.pooling()` first — which dlt does not do, since these defaults are
  	# already sane for our workload. The pool matches purely on connection-string text, so
  	# our credentials building a stable DSN is what makes reuse actually happen.
  	self._conn = mssql_python.connect(
  	self.credentials.to_odbc_dsn(),
  	autocommit=True,
  	attrs_before=self.credentials.to_odbc_attrs_before(), # type: ignore[arg-type]
  	timeout=self.credentials.connect_timeout,
  	)

mssql-python v1.13.0 / PR #603 adds a public token_provider= parameter accepting any synchronous object with .get_token(scope). It centralizes token acquisition, validation, expiry capture, error translation, token struct construction, and fresh-token acquisition for bulk-copy operations.

Our azure_credential objects already implement this protocol, including FabNotebookUtilsCredential. Continuing to serialize those credentials in dlt duplicates driver code and prevents the driver from retaining the provider for later operations.

This depends on #10.

Proposed change

1. Pass MsSqlCredentials.azure_credential to mssql_python.connect(..., token_provider=...).
2. Keep access_token on the existing attrs_before[SQL_COPT_SS_ACCESS_TOKEN] path:
   • it is explicitly a pre-acquired token rather than a provider;
   • it preserves the v1.13 escape hatch for sovereign-cloud tokens, because token_provider uses the Azure commercial SQL scope.
3. Split the current helper so only access_token needs UTF-16LE/length-prefix packing; remove manual get_token(SQL_TOKEN_SCOPE) and its dlt-owned scope constant from the azure_credential path.
4. Continue omitting Authentication=, UID, and PWD from the DSN when either explicit token mechanism is selected.
5. Keep the existing precedence explicit: access_token wins over azure_credential, which wins over authentication.

Do not claim that token_provider makes token-hash pools expiry-refreshable. In v1.13 custom providers are still keyed by the token hash; token rotation opens a new pool bucket. The concrete value here is a smaller dlt authentication surface, driver-owned validation/lifecycle metadata, and correct token reacquisition for the new bulk-copy API.

Acceptance criteria

• azure_credential is passed unchanged as token_provider and dlt does not call get_token() while building credentials or connection arguments.
• access_token still produces the exact binary attrs_before value and never sets token_provider.
• authentication=ActiveDirectory* continues to use only the DSN and never sets either explicit token argument.
• FabNotebookUtilsCredential("sql"), DefaultAzureCredential, and a minimal custom get_token(scope) provider are covered.
• Tests cover precedence and prove that token_provider, Authentication=, and access-token attrs_before are never sent together.
• Driver acquisition failures retain the original credential error as their cause and are mapped through the existing dlt database exception boundary.
• Documentation calls out that token_provider supports only the Azure commercial SQL scope; sovereign clouds require access_token.

[sdebruyn]

Branch, upstream PR, integration, and release plan

Where this change belongs

Implement this issue on the existing feat/mssql-python-driver branch and therefore in dlt-hub/dlt#4141, after #10 has raised the driver baseline.

Although the public access_token and azure_credential fields originate in feat/mssql-access-token-credential / #4147, do not implement token_provider= there. dlt-hub#4147 must remain a valid pyODBC authentication PR. Passing a credential to mssql_python.connect(token_provider=...) is specifically part of the later driver migration and therefore belongs in its child:

upstream/devel
└── feat/mssql-access-token-credential        #4147
    └── feat/mssql-python-driver               #4141  <-- implement #10, then #11
        └── feat/2-mssql-python-arrow-batches #4258

Use a separate focused commit for #11 on the dlt-hub#4141 branch. This preserves review history while avoiding a duplicate authentication implementation on the parent branch.

How it reaches upstream

Pushing feat/mssql-python-driver updates the existing upstream PR dlt-hub#4141 automatically. No separate upstream PR is needed for #11.

The dlt-hub#4141 description should state both facts clearly:

• it is stacked on the current feat(mssql,fabric): add Entra ID authentication and NotebookUtils credential dlt-hub/dlt#4147 branch, even though its GitHub base remains dlt-hub/dlt:devel;
• it changes the feat(mssql,fabric): add Entra ID authentication and NotebookUtils credential dlt-hub/dlt#4147 pyODBC token implementation only after the driver migration, by delegating azure_credential to mssql-python while retaining explicit raw-token compatibility.

After dlt-hub#4147 lands, rebase dlt-hub#4141 onto current upstream/devel and have an authorized maintainer update the fork branch with force-with-lease so upstream reviewers see only the driver-specific diff.

When #10 and #11 are complete on dlt-hub#4141, rebase every child branch onto that exact tip:

• existing feat/2-mssql-python-arrow-batches / upstream PR feat(sql_database): add mssql_arrow backend for native arrow batches dlt-hub/dlt#4258;
• the future feat/13-mssql-python-arrow-bulk-copy branch described in mssql: use native Arrow bulk copy for Parquet load jobs #13.

This matters for #13 because bulkcopy_arrow must retain the credential provider in the connection object so the driver can acquire a fresh token for the bulk operation.

How it reaches integrated-all-prs

Never patch integrated-all-prs directly. Rebuild it from current upstream/devel and merge leaf branches.

Before #13 exists, merging the rebased feat/2-mssql-python-arrow-batches leaf is enough: its ancestry brings dlt-hub#4147, dlt-hub#4141, #10, and #11 together exactly once.

After #13 exists, merge both driver children:

feat/2-mssql-python-arrow-batches
feat/13-mssql-python-arrow-bulk-copy

They share the same updated dlt-hub#4141 parent, so the authentication commits are not duplicated. Continue with the independent branches listed in the README and run the combined authentication, destination, Arrow, formatting, typing, and lock checks.

How it reaches dlt-fabric

The published branch is rebuilt separately from upstream/master; never merge the devel-based integration branch into it.

After integration validation:

1. Rebuild dlt-fabric from the latest stable upstream/master.
2. Cherry-pick the non-merge feature commits from devel..integrated-all-prs in dependency order. The mssql: delegate TokenCredential authentication to mssql-python #11 commit must follow the mssql: raise the mssql-python baseline to 1.13 #10 baseline commit and the underlying feat(mssql,fabric): add Entra ID authentication and NotebookUtils credential dlt-hub/dlt#4147/feat(mssql): replace pyODBC + ODBC installation dependency with Microsoft's mssql-python dlt-hub/dlt#4141 authentication/migration commits.
3. Cherry-pick the separate package-name/version commits and assign the next .postN version.
4. Validate SQL login, every named Authentication= method, raw access_token, azure_credential, and fab_notebookutils on the release branch.
5. Verify that no commit already contained in devel leaked into the upstream/master-based branch before publishing.

The implementation must flow from dlt-hub#4141 into the derived branches. A direct fix on integrated-all-prs or dlt-fabric would be lost on the next rebuild and would not update the upstream review.