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!