Add support for Microsoft Entra Authentication (formerly Azure Active Directory)

[Michoels]

Background

Azure SQL Database supports two forms of authentication:

• SQL authentication (with a username and password)
• Microsoft Entra authentication This used to be called Azure Active Directory.

In July of 2023, Azure Active Directory was renamed to Microsoft Entra ID. However, much of the documentation still refers to it as Azure Active Directory or simply Azure AD.

Entra ID supports a number of different authentication techniques, as seen in this screenshot of SQL Server Management Studio:

All Entra logins are managed by the Microsoft Identity platform.

On a technical level, Entra ID works by obtaining an OAuth token from Microsoft Authentication Library (MSAL).

That token is then submitted to the server by using the TDS FEDAUTH stream

This is documented in the TDS protocol docs here.

Suggested changes

I suggest adding an additional login parameter for providing a valid OAuth token that can be used to authenticate to SQL Server.
Maybe authToken?

It would be up to the user to obtain the token by using their own copy of Microsoft Authentication Library (MSAL). Once they have the token, they'd provide it as a login parameter to FreeTDS, which would then authenticate as specified in the TDS protocol.

Microsoft seems to prefer Entra ID (it's the default for Azure SQL Database). Entra ID also adds a bunch of improved security features, including true MFA, security auditing, and the ability to track and revoke credentials without needing to muck about in the actual database.

Seeing as Entra ID is likely the future, it's probably worth implementing.

Thanks for this awesome lib!

[Michoels]

This is the same underlying issue as #360

[freddy77]

Sound doable. It will take some time. I'm curious, how to you login with this authentication using Windows? More from a use prospective than code wise.

[Michoels]

I'm not 100% certain (I'm a Mac guy), but I think the idea is that the user is already logged in to Windows with a valid Microsoft account. If the user selects Windows authentication, then MSAL hooks into the Windows auth API to obtain tokens for the currently logged in user.

The upside is the user doesn't need to log in again; the downside is they can only log into a single account - their own.

[freddy77]

Doing some research on this. It looks like the TDS documentation is wrong, but I suppose/hope they just swap 2 fields in the login description. I managed to use MSAL (Python) to get a ticket using some requests similar to SSMS, currently not the way ODBC is working. For the client id you need to use some not really documented values. I found a forum post with all "hidden" client IDs used by various libraries (ADO.NET, OleDB, ODBC, SSMS, JDBC, PowerShell and some others). You cannot easily provide a token, the client seems to stop the login, open a web page and if it's fast enough can complete the login with a single TCP connection.

The process I uses was pretty interactive, not sure how to do it not interactively. You could use the password but it seems not encouraged by Microsoft. Do you know any way to setup a not interactive account/client/whatsoever ?

[freddy77]

Got some replies from Microsoft. The fields are indeed inverted in the documentation (fFedAuthEcho and bFedAuthLibrary).
The workflow 3 for ADAL indicates AAD interactive mode (see https://learn.microsoft.com/en-us/answers/questions/1421655/fedauth-login-feature).

[rplescia]

I have already commented on #360 but this is sorely needed

[freddy77]

@rplescia what is your user case? How are you going to use it? I mean, how are you logging in? Do you use a password?

[Michoels]

My main use case is accessing Azure SQL Database from Rails with the Rails SQL Server adaptor, which relies on FreeTDS.
With default settings, Azure SQL Database only supports logging in with Entra ID, so I cannot connect.

Enabling SQL password authentication requires mucking about in the Azure console, which I don't always have permissions for.

Second, SQL username + password login makes the security team nervous.
It's the only login that can't be secured with MFA.
A compromised or leaked DB would be catastrophic.

At my current job we had no choice and had to enable SQL password authentication with a really long password and an IP whitelist. It would be great if we could migrate to Entra ID which is much more secure.

[Michoels]

I just learned that Microsoft's Azure Data Studio desktop app implements this.
Azure Data Studio is fully open source (repo here).

Maybe we can study their code to see how Microsoft does it?

[daviewales]

This is how it works with PyODBC:
https://docs.sqlalchemy.org/en/20/dialects/mssql.html#connecting-to-databases-with-access-tokens

I'm guessing that because FreeTDS controls the underlying TDS implementation, it might be possible to make it a lot cleaner than the PyODBC example above.

Note that a common (and convenient) method for acquiring a token (at least in Python and perhaps other languages) is to use DefaultAzureCredential. This automatically detects credentials available in your environment, then returns an object with a get_token method. You can see this used in the PyODBC example above.

DefaultAzureCredential can generate tokens from credentials stored in environment variables, from Azure CLI, and a bunch of other places.

Note that tokens have a limited lifetime, so there will need to be some way to refresh them as required.

Once FreeTDS supports creating connections with Entra tokens, it will unblock downstream projects such as pymssql.

This is some more related documentation, but perhaps not relevant as it relates specifically to MS ODBC. (Search SQL_COPT_SS_ACCESS_TOKEN):
https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver16#new-andor-modified-connection-attributes