Add support for Microsoft Entra Authentication (formerly Azure Active Directory)
Michoels opened this issue · 9 comments
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!
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.
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.
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 ?
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).