How to use Azure Applications appRoles (defined in application manifest) as Roles in Azure SQL

Question

Our team would like to use application roles defined inside the Application manifest when registering an Application inside of the Azure Portal. Here's a sample from our TestApp's manifest.

  "appRoles": [
  { "allowedMemberTypes": [
      "User",
      "Application"],
    "displayName": "My App Role 2",
    "id": "0993b354-6b2f-471d-bba2-f7467a1bbbf2",
    "isEnabled": true,
    "description": "My App Role description for MyAppRole2",
    "value": "MyAppRole2" },
  { "allowedMemberTypes": [
      "User" ],
    "displayName": "My App Role 1",
    "id": "0993b354-6b2f-471d-bba2-f7467a1baaf2",
    "isEnabled": true,
    "description": "My App Role description for MyAppRole1",
    "value": "MyAppRole1" }]

These application roles show up as role claims in web apps allowing developers to control access to endpoints by decorating the endpoint as shown below.

[Authorize (Roles = "MyAppRole2")]
public class AdministrationController : Controller
{
}

Through the Azure Portal (under Enterprise Apps), we can assign users and/or groups to roles for the specific application and everything works well. However, when attempting to access the database directly (from SSMS), membership for application roles does not work.

CREATE USER [TestApp] FROM EXTERNAL PROVIDER
CREATE ROLE [MyAppRole2] AUTHORIZATION [TestApp1]

We have also tried

CREATE ROLE [My App Role 2] AUTHORIZATION [TestApp1]

In either case, checking for membership at the database level returns '0' false.

SELECT IS_MEMBER('MyAppRole2') [My App Role 2]

However, if we create an AAD Group (e.g. 'My Group For TestApp My App Role 2'), assign the 'My App Role 2' role to the Group through the Enterprise Apps interface, and assign the user to the group through AAD Users and Groups interface...we can see the membership to the group by using groups in SQL.

The following creates the relationship between Azure SQL and the group security principal in AAD.

CREATE USER [My Group For TestApp My App Role 2] FROM EXTERNAL PROVIDER

Any user logging into SQL directly who is in that group, will show as a member of that group. The following returns true for an authenticated SSMS user who is also a member of the AAD group.

SELECT IS_MEMBER('My Group For TestApp My App Role 2')

Although this works, if we remove the Application Role from the Group (under Azure Enterprise Applications), the above query still returns true. This is because the user is still a valid member of the group even though the group no longer has access to the Application Role. Therefore, this work around isn't valid because the database isn't actually validating that the user participates in the application role.

How can we directly associate application roles to database roles to ensure proper security?

Answer 1

Based on my understanding, you were mixing the application roles defined in the Azure AD app with Application Roles in SQL Server.

If you want to manage the Azure AD service principal using roles. We can use the command below to create an database role and assign the role to the service principal create in Azure AD:

CREATE ROLE customRole2

alter role customRole2 add member FeiTestApp
--FeiTestApp is the service principal in Azure AD
EXECUTE AS USER = 'FeiTestApp';  

SELECT IS_MEMBER('customRole2')
REVERT;

The command should return 1, and then we can also use the access token to check the result like below:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder["Data Source"] = "xxxx.database.windows.net"; // replace with your server name
builder["Initial Catalog"] = "DBTest"; // replace with your database name
builder["Connect Timeout"] = 30;

string authority = "https://login.microsoftonline.com/{0}";
string tenantId = "microsoft.onmicrosoft.com";
string clientId = "";
string secrect = "";
string resourceId = "https://database.windows.net/";
AuthenticationContext authContext = new AuthenticationContext(string.Format(authority,tenantId));
var accessToken=  authContext.AcquireTokenAsync(resourceId, new ClientCredential(clientId, secrect)).Result.AccessToken;

using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
    try
    {
        connection.AccessToken = accessToken;
        connection.Open();         
        SqlCommand cmd = new SqlCommand("SELECT IS_MEMBER('customRole2')", connection);

        var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine($"{reader[0]}");              
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

More detail about Azure SQL database roles management, you can refer document below:

Principals (Database Engine)