Azure App service authentication - SQL database client sharding

Question

We have an azure SQL database that will contain multiple client's data. Each table has an account Id which we were planning on using use to seperate client data. We are displaying the data via an Azure App service and an bff middleware in azure function app. We were planning on adding Azure App Service Authentication to authenticate users into our web app.

However we cannot find documentation on how to store an account Id against an authenticated user; so that we could return results from the database specific only for that user/client?

I have done this before in a web app and you need to add a "claim". This is my question with a great answer. You create a mapping table that maps the login email to your ID. Then at login time you go look up that id and attach it as a claim in the security structure. https://stackoverflow.com/questions/43343399/capturing-login-event-so-i-can-cache-other-user-information — Nick.Mc, Sep 11 '20 at 04:49
@JosephXu a lot of them. The others have Ids that link to tables with account Id that makes sense. Its our unique key per customer — will wainwright, Sep 11 '20 at 07:00

score 0 · Answer 1 · answered Sep 11 '20 at 08:57

App Service passes user claims to your application by using special headers. External requests aren't allowed to set these headers, so they are present only if set by App Service.

There are two ways to get the usename(Account id to login).
1.You could use X-MS-CLIENT-PRINCIPAL-NAME as http resquest header to get the username.

var name1=httpRequest.Headers["X-MS-CLIENT-PRINCIPAL-NAME"].ToString();

2.You can retrieve the authenticated user information from the ClaimsPrincipal instance injected in the Run method.

public static async Task<HttpResponseMessage> Run(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)]
    HttpRequest httpRequest, 
    ILogger logger, 
    ClaimsPrincipal claimsPrincipal)
{   
    var name2 = claimsPrincipal.Identity.Name;
}

After get the username(Account id to login), you can add it to the conditions of the sql statement.

Note:

When you add App registrations in Azure ad, add redirect url as https://yourfunctionname.azurewebsites.net/.auth/login/aad/callback and click ID token when you setting Advanced settings.

Azure App service authentication - SQL database client sharding

1 Answers1