Support passwordless authentication to Azure hosted MsSQL

[jfgreen-liberis]

Feature description

Allow MsSQL destination to support passwordless authentication via a token.

This token is typically obtained using azure.identity.DefaultAzureCredential() but it can also be obtained via other methods like OIDC.

Are you a dlt user?

Yes, I'm already a dlt user.

Use case

I'm looking to get DLT setup reading/writing to MsSQL running in Azure and am hoping to use a passwordless connection.

Proposed solution

Looking at the current implementation, this might be as simple as modifying MsSqlCredentials to accept either a username/password or a token. If present, this token would then be included in pyodbc.connect via the attrs_before argument. If this sounds like an OK approach I am happy to draft a PR.

I experimented with doing a slightly underhanded runtime patch of pyodbc.connect with the added token and this seemed to work OK with DLT loading a table quite happily.

Related issues

No response

[jfgreen-liberis]

It has just occurred to me that this feature would ideally need to handle expired tokens, either by having the user supply a callback function or by DLT natively working with Azure Credential/Token objects to refresh tokens automatically.

Microsofts documentation says that by default tokens will be "a random value ranging between 60-90 minutes", which could easily be exceeded by long running pipelines.

[rudolfix]

@jfgreen-liberis it would be cool if you could submit your solution. we'll most probably take it over and write some tests for it. btw. we have AzureCredentials object that can generate tokens or take the machine defaults... maybe mssql destination should simply accept both. We'll see one we see your PR. thanks!

[jfgreen-liberis]

Hi. Never got around to that PR sorry!

Unlikely to get the time to pursue this for the foreseeable future, so here is our workaround in which we construct an SqlAlchemy engine upfront:

def build_sql_server_engine(
    host: str, database: str, credential: TokenCredential, port=1433
) -> Engine:
    def provide_token(dialect, conn_rec, cargs, cparams):
        # remove the "Trusted_Connection" parameter that SQLAlchemy adds
        cargs[0] = cargs[0].replace(";Trusted_Connection=Yes", "")

        scope = "https://database.windows.net/.default"
        token_bytes = credential.get_token(scope).token.encode("UTF-16-LE")
        token_struct = struct.pack(
            f"<I{len(token_bytes)}s", len(token_bytes), token_bytes
        )
        sql_copt_ss_access_token = 1256  # Defined by microsoft in msodbcsql.h
        attributes = {sql_copt_ss_access_token: token_struct}
        cparams["attrs_before"] = attributes

    base_url = URL.create(
        "mssql+pyodbc",
        host=host,
        database=database,
        port=port,
        query={
            "driver": "ODBC Driver 18 for SQL Server",
            "TrustServerCertificate": "yes",
        },
    )

    engine = create_engine(base_url)
    event.listen(engine, "do_connect", provide_token)
    return engine

engine = build_sql_server_engine(
    host=some_host,
    database=some_database,
    credential=DefaultAzureCredential()
)

resource = sql_table(
    credentials=engine,
    schema=some_schema,
    table=some_table,
)

Hope that's at least somewhat helpful to anyone looking into this.

[JohnMav]

+1 to this functionality, and similar to this request it would be nice to be able to use Passwordless auth via DefaultAzureCredential() for when the destination filesystem is Azure Blob Storage.