Is Azure SQL/AAD supported?

[JocaPC]

In the documentation I cannot find does it works with Azure SQL and could we use AAD auth. The table of supported versions lists only SQL Server versions and all examples use SQL auth.

https://github.com/prisma/tiberius#supported-sql-server-versions

[esheppa]

Hi @JocaPC - I can confirm that tiberius works with Azure SQL, however AAD auth is not yet implemented. It is simplest to use with the proxy method of connection, I'm not sure whether it works with the redirect method

[pimeys]

Yeah, and I'd say support for AAD auth will happen eventually. I know our users already need it, and I'm estimating to have this on my desk at some point.

But right now it is not a priority, and if you need the support, would be great to either get a PR or at least some research how this should work and how much code is needed to Tiberius.

[x2bool]

Hi! I've been using Tiberius for a while for a toy project, and I have .NET/Microsoft stack background, and I know some things about AAD and its protocols.

I am willing to contribute this feature, but my experience with Rust is limited.

My question is: does it make sense to contribute if my work would require multiple (probably many) rounds of review, and I could make some basic (and maybe stupid) Rust mistakes? Would someone be willing to review such PR?

[pimeys]

Sure thing. Could you maybe first describe a bit how you would implement this, what protocols and libraries you'd need?

I'm mostly interested outlining on how the authentication works here, does it use some existing protocols we might already use in Tiberius, or if not is there some ready-made libraries available in the Rust ecosystem...

[x2bool]

Great!

AAD has multiple auth modes. Maybe it's wise to take just one or two flows right now. I will prepare more structured info, and do some research, after that I will return here so we could discuss details.

Thanks!

[pimeys]

Yep, make the MVP of the feature first. As little code as possible. I'll review and give guidance if needed.

[rogerpengyu]

what's the latest of this issue? I am in need of this and happy to help to code it up.

[rogerpengyu]

ping on this topic

[pimeys]

Would be nice to understand what this means, how should it work and all that. I know our users have been asking for the support too, so yeah it would be great to have...

[rogerpengyu]

in my case, I need to use AAD service principal to auth to azure SQL. From azure SQL it will take a jwt token as auth. There is an implementation for the go-mssql package: https://github.com/denisenkom/go-mssqldb

I will come up with a PR. If there was some pre-work let me know.

[pimeys]

Do we need http for authentication? If so, it should work agnostic to the runtime and hopefully not bring a bloaty http crate as a dependency...

[rogerpengyu]

no. for auth customer should get the jwt token by themselves, and just provide the token to tiberius. this is how the go package works as well.

[pimeys]

Sounds like an easy add. Can we test it in the CI?

[rogerpengyu]

I don't know how our CI SQL was setup. Do we use an Azure SQL for testing or a standalone sql instance? It only works with Azure SQL.

[pimeys]

We have the Azure SQL docker image in the compose setup, but I don't know can you set it up to have this auth option available.

Prisma the company can of course do an integration test against Azure SQL, but that's kind of a long response cycle...

[janpio]

Just for context: This is what you are all talking about here: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview?view=azuresql

[1Dragoon]

no. for auth customer should get the jwt token by themselves, and just provide the token to tiberius. this is how the go package works as well.

IMO that's the only way to do it. AAD authentication is a moving target. Not sure why MS hasn't created an MSAL4Rust variant yet given their other Rust efforts.

[rogerpengyu]

Please take a look at the PR. I tested with my local setup. It takes an AAD token.

[rogerpengyu]

could someone take a look at the PR?

[rogerpengyu]

should this issue be closed?

[dtheodor]

I think this should not yet be closed, since Azure AD auth provides several different ways of authenticating, such as AD access token, AD service principal credentials, AD managed identity, AD user credentials. It looks like you implemented AD access token which is great. The rest are still useful to have.

[pimeys]

How do all of these methods work? If they end up getting an access token, I think it's still enough for Tiberius if we only support that.

What I'm after is that many of these methods require you to do network IO, meaning we need to take the runtime into account in Tiberius and that is off the limits for the crate.

What would be beneficial is to somebody to start a new crate for AD authentication we could then use together with Tiberius, and here we'd only support the authentication options that talk directly with the database.

[esheppa]

@dtheodor this works fine with eg, service principal, you can use https://docs.rs/azure_identity/latest/azure_identity/ to get tokens for each of the implemented flows

How do all of these methods work? If they end up getting an access token, I think it's still enough for Tiberius if we only support that.

AFAIK they all result in getting an access token

What would be beneficial is to somebody to start a new crate for AD authentication

azure-identity mentioned above suits this purpose perfectly. Any missing flows could be PR'd there if needed

[dtheodor]

I don't know if all of these use Oauth2 tokens behind the scenes or some other protocol. I am looking at this from the perspective of how other sql server clients do it, such as odbc, jdbc, ado.net which all provide direct support for all authentication methods.

[esheppa]

Those other clients also likely run IO internally which tiberius doesn't do, so they would be able to make HTTP calls or similar whereas in tiberius it would have to be implemented in a seperate crate. Do you have a specific use case which isn't supported by azure identity?

[dtheodor]

After a cursory glance at TDS it appears that you implemented the federated authentication token which covers all (?) AD authentication scenarios. So I think this is fine, your library is a TDS implementation and I should not expect it to be as heavyweight as odbc etc.

[pimeys]

Again coming back to this, but I've been thinking about a new crate tiberius-with-batteries-included, that would take Tokio and just make the full package out using tiberius as the core for TDS. We would then be able to have a nice transaction apis and all that.

The problem is my time is super limited nowadays, and Prisma as a company needs just the TDS part, so I'm not having the time allocated to start implementing new crates.

[TheCrustyCrab]

@pimeys , @Weakky could the integration of access token retrieval (and thus an http client) still be considered for tiberius? I realize you prefer to keep the crate runtime agnostic and just a pure TDS implementation but in fact federated authentication is also dependent on TDS. The AAD token implementation from @rogerpengyu requires a developer to know the tenant id and scope (next to the client id and secret), while those are foreseen by the server in the FEDAUTHINFO packet after the client indicates to use federated authentication with the "MSAL" workflow.

Regarding prisma, in https://github.com/prisma/prisma/issues/13853#issuecomment-1160119742 you suggested to implement the token retrieval in quaint, but how could one pass the tenant id and scope to it? (maybe via custom properties in the connection string, but that doesn't sound like a clean practice) In my opinion, it makes most sense to let tiberius get this information via TDS and retrieve and send the token to establish the connection.

I've made an implementation of this (managed identity and service principal authentication) in tiberius in the fork below: https://github.com/prisma/tiberius/compare/main...TheCrustyCrab:tiberius:aad_auth

It makes use of the azure_identity and azure_core crates from the unofficial Azure SDK for Rust and has thus a dependency on reqwest/tokio, however I tried my best to isolate it in an optional feature (currently called "aad"). Those crates can work with other http client libraries if needed, by providing a façade azure_core::HttpClient implementation.

This supports connection strings like the following (not exhaustive): jdbc:sqlserver://myazuredb.database.windows.net:1433;encrypt=true;authentication=ActiveDirectoryManagedIdentity jdbc:sqlserver://myazuredb.database.windows.net:1433;encrypt=true;authentication=ActiveDirectoryManagedIdentity;user=my-user-managed-identity jdbc:sqlserver://myazuredb.database.windows.net:1433;encrypt=true;authentication=ActiveDirectoryServicePrincipal;user=my-sp;password=my-secret

I've tested it on a Azure Virtual Machine (Windows 10), Container Apps and App Services with success.

Note that an access token has a limited lifetime (sometimes configurable in Azure AD), but should only be valid during connection establishment. An established connection remains open even after the token expires, so there's no need to refresh/reconnect.

Any feedback would be welcome!

[pimeys]

Hey. I am not working for Prisma anymore. Somebody from the company could maybe chime in to see if this could be done.

Ping @Weakky

[itpropro]

Any updates on this? Support for managed identities with Azure SQL is becoming mandatory in more and more environments and it would be great if prisma could support this soon.