Active Directory B2C onBehalfOf authentication to SQL Azure

Question

I have an application that is authenticating users against Azure Active Directory. The returned JWT token is then passed from the application into a Web API that validates the token.

I then ask Active Directory to generate another JWT token on behalf of the user for SQL Azure. The Web API can now authenticate to SQL Azure with the OnBehalfOf token. From here the database is implementing RLS (Row Level Security) by using some unique information in the JWT to filter the results accordingly.

For this to work, the Active Directory application used to request the onBehalfOf token must have requiredResourceAccess for SQL Azure. This is done by editing the application manifest in the Azure portal specifying that SQL Azure (022907d3-0f1b-48f7-badc-1ba6abab6d66) can Access Azure SQL DB and Data Warehouse (c39ef2d1-04ce-46dc-8b5f-e9a5c60f0fc9).

"requiredResourceAccess": [
    {
      "resourceAppId": "022907d3-0f1b-48f7-badc-1ba6abab6d66",
      "resourceAccess": [
        {
          "id": "c39ef2d1-04ce-46dc-8b5f-e9a5c60f0fc9",
          "type": "Scope"
        }
      ]
    }
  ]

I want to support social logins and Azure B2C is generally a better fit for my requirements. But is it possible for Azure Active Directory B2C to support this authentication flow?

const string tenant = "...";
const string clientId = "...";
const string clientSecret = "...";
const string connectionString = "Server=...database.windows.net,1433;Initial Catalog=...;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False";

async Task Main()
{
    try
    {
        var authenticationContext = new AuthenticationContext(TenantSettings.Authority);

        var result = await authenticationContext.AcquireTokenAsync(
            "https://database.windows.net/",
            new ClientCredential(clientId, clientSecret),
            new UserAssertion("eyJ...")).ConfigureAwait(false);

        await ConnectToDatabaseAsync(result.AccessToken.Dump());
    }
    catch (Exception ex)
    {
        $"Failed to connect to Azure SQL.\n\tReason: {ex.Message}".Dump();
    }
}

private static async Task ConnectToDatabaseAsync(string accessToken)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.AccessToken = accessToken;
        await connection.OpenAsync().ConfigureAwait(false);

        using (var command = new SqlCommand("select USER_NAME()", connection))
        using (var reader = await command.ExecuteReaderAsync())
        {
            await reader.ReadAsync().ConfigureAwait(false);
            reader.GetString(0).Dump("User");
        }
    }
}

private static class TenantSettings
{
    public const string Authority = Instance + Tenant;
    public const string Instance = "https://login.microsoftonline.com/";
    public const string Tenant = tenant;
}

Tom, are you using the appRoles defined in your Application Manifest as Database Roles in Azure SQL? Could you provide your DB scripts in your example? e.g. CREATE USER X FROM EXTERNAL PROVIDER; and, CREATE ROLE myAppRole AUTHORIZATION X? — Greg Grater
CREATE USER [X] FROM EXTERNAL PROVIDER - Yes, that is exactly how I've approached it. The role in the app manifest is to enable the on-behalf-of flow for a user to access the database when acquiring a token for SQL Azure. Fei Xue has confirmed that unfortunately this flow isn't supported by B2C. — Tom Austin
Tom, thanks for the reply. I'd like to know how you determined the guid's for the resourceAppId and resourceAccess.id values. Are one of these guid's the object Id of a specific azure database and the other the object Id of the Azure SQL? Can you edit the post and supply your entire app manifest? I think we are on similar paths, but I'm going to avoid B2C by using a second AAD that is trusted by the Application's AAD. — Greg Grater
Although I've been able to use Group membership in AAD to restrict access in SQL, my hope is that I can use the appRoles array defined in the application manifest as identifiers in SQL (either as roles or principals). This would allow assignment of Users/Groups to Application Roles in the Azure Portal and enforcement of those assignments at the data layer. Like I said, I have this working for Group membership, but not for Application Roles defined in the application manifest appRoles array. Thoughts? — Greg Grater
Authenticate public clients to SQL Azure provides information on a missing piece to authenticate SQL Azure using AAD. 022907d3-0f1b-48f7-badc-1ba6abab6d66 is the GUID for SQL Azure and c39ef2d1-04ce-46dc-8b5f-e9a5c60f0fc9 is the GUID for "can Access Azure SQL DB and Data Warehouse". These are needed to get a token for the database. — Tom Austin

Fei Xue - MSFT Fei Xue - MSFT · Accepted Answer · 2017-03-07T07:25:01

The on-behalf-of flow is not currently implemented in the Azure AD B2C. Please refer Azure Active Directory B2C: Types of applications(Current limitations).

If you have any idea or feedback about Azure, you can submit it from here.

Active Directory B2C onBehalfOf authentication to SQL Azure

1 Answers