Migrate the MS SQL family destinations (mssql, synapse, fabric) from pyodbc to mssql-python

[sdebruyn]

Feature description

Switch the mssql, synapse, and fabric destinations from pyodbc to Microsoft's official mssql-python driver.

Use case

The MS SQL family destinations connect through pyodbc, which needs a system-installed ODBC driver (msodbcsql18) and unixODBC. That system dependency is the main friction point:

• it has to be installed and kept in sync on every developer machine and CI runner, with platform-specific steps;
• it complicates ARM and macOS setups;
• it makes running the destination integration tests locally (for example in Docker) harder than it needs to be.

Proposed solution

Migrate to mssql-python, Microsoft's DB-API driver that bundles its own native driver, so the msodbcsql18/unixODBC system dependency goes away. The connection model stays the same, including Entra ID access-token injection via attrs_before.

Behavior differences the migration accounts for:

• the driver is bundled, so no DRIVER= keyword and no msodbcsql18 install are needed;
• long/max types (for example varchar(max)) are handled natively, so the LongAsMax connection keyword is dropped;
• the connection-string parser only accepts a fixed set of keywords, so unsupported keywords are removed rather than passed through;
• exception mapping uses the driver's stable driver_error label instead of parsing message text.

Related issues

N/A

[sdebruyn]

This is available now as a maintained fork package on PyPI: https://pypi.org/project/dlt-fabric/.

It migrates the mssql, synapse, and fabric destinations to Microsoft's mssql-python driver, which bundles its own client libraries, so no separate pyODBC or ODBC Driver 18 installation is required. The change is proposed upstream in #4141.

The fork tracks dlt's releases and stays maintained until the upstream PRs (#4140, #4141, #4142, #4147) are merged, after which it is no longer needed. Install dlt-fabric in place of dlt (pip install dlt-fabric / uv add dlt-fabric); the import stays import dlt.

[rudolfix]

@sdebruyn this looks pretty cool! I have a few questions:

1. looking at comment above: you are running this in production? did you test mssql/fabric/synapse? with which authentication methods? for us it will be impossible to test them all
2. are your changes fully backward compat? for example - will "Driver" be still tolerated? we'll need to make sure that existing users still continue to work as before
3. it looks to me that changes from other PRs (authentication method extensions) leaked into this PR. or those were enforced by new driver?
4. there are many very long docstrings and comments. also blocky separator comments are used. you could ask your LLM to review the code and follow our code style... this is a known problem and without intervention such artifacts are produced