Accessing Azure SQL database with Azure Active Directory contained database users through Firebase authentication

Question

I have a Next.js web app that authenticates users through Firebase using the "microsoft" provider, which is connected to my Azure Active Directory. I want to give users access to my Azure SQL database, but restrict data access based on their AD identities. I learned that "contained database users" mapped to Azure AD identities can be used to assign permissions and restrict access. However, I want to enable access to the database silently after users authenticate through Firebase, without requiring a separate AD authentication. Is this possible?

Answer 1

From what you provided, I understood that you're trying to bridge two authentication systems:

Your Next.js app users log in via Firebase using the Microsoft provider. Firebase doesn't know the specifics about Azure AD, but it provides a token to the user that proves their identity to Firebase's ecosystem and Azure SQL can authenticate users based on their Azure AD identity.

This is what I suggest : your users log into your app using Firebase with the Microsoft provider. This, behind the scenes, will authenticate them with Azure AD and get a token which Firebase uses to validate their session.

Once authenticated, your app backend (which might be an API you've built, serverless functions, etc.) should perform any additional logic. If you need to access the Azure SQL database on behalf of the user, you'll face a challenge: Firebase's token won't be valid for Azure SQL's Azure AD authentication.

To bridge the gap, I think you can use a service principal (a kind of 'service account' in Azure AD). This service principal would be given rights in your Azure SQL database, and your backend would authenticate to Azure SQL using this service principal.

And of course to ensure users only see their own data, upon successful Firebase authentication, fetch the user's Azure AD identity details (like their UPN or object ID) from the Firebase authentication token. Use these details as a key into your database (essentially mapping Firebase-authenticated users to their data in Azure SQL), and only fetch data based on this identity.

To add an additional layer of data access security, you can use Row-Level Security (RLS) in Azure SQL. This would allow you to define policies on who can see what data based on their Azure AD identity. However, since you'd be using a service principal to access the data, you'd need to design your policies so that the service principal's rights are combined with the fetched Azure AD identity from Firebase to get the right data set.

Last thing, If you wanted users to directly access Azure SQL using their Azure AD identities (and not a service principal on their behalf), they'd have to re-authenticate using Azure AD (which you're trying to avoid). Plus, giving direct database access to end users is usually not recommended due to security and performance concerns.