Azure App Service with Azure SQL DB (Managed Identity)

Question

I am trying to set up connectivity from azure app service to azure sql db using managed identity (System assigned)

Here is the set up: Two azure app services one for app and one for api in one service plan. I have a SQL server in Azure with two azure sql db. The app services have their system assigned managed identity turned on. I have app settings for multiple environments in both the project (app and api).

appsettings.json, appsetings.development.json, appsettings.acceptance.json, appsettings.production.json

When I explored Kudu after deploying my app to app service from yaml pipeline I see all the app settings files are available.

The configuration ->application setting in Azure app service do not have any variables apart from the app insight instrumentation key which I add (IaC) in template while provisioning app service in Azure.

Managed identity for both app services have been assigned db roles in both databases db1 and db2

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];

In my app setting json file I have two connection strings for both DB

"connectionStrings": {
  "DB1Context": "Server=myserver.database.windows.net;Authentication=Active Directory Managed Identity;Encrypt=True;Database=db1;",
"DB2Context": "Server=myserver.database.windows.net;Authentication=Active Directory Managed Identity;Encrypt=True;Database=db2;",

Program.cs (Reading connection string)

builder.Services.AddHangfire(options =>
{
  options.UseSqlServerStorage(builder.Configuration.GetConnectionString("DB1Context"),
   new SqlServerStorageOptions
   {
   });
});

builder.Services.AddDbContext<DbContext>(options=>
{
 options.UseSqlServer(builder.Configuration.GetCOnnectionString("DB2Context"));
});

Questions:

1. On executing dotnet webapp.dll from Console (within Azure) I get the below error: Unhandled Exception. System.ArgumentException.Keyword not supported: 'authentication'. Note: I am using EFCore and also have added the package Microsoft.Data.SqlClient (latest version 5.x.x) in both projects.

2. If all the app settings file are pushed (as seen in Kudu), which app setting will be read? appsettings.json or any other environment specific? As I understand it should be appsettings.json.

3. I have tried other variants of connection string with Authentication value changed(Default, Integrated, etc.) but seeing the same error. I have also tried to add the connection string in Azure App Service -> Configuration ->application setting (also in connection string section) but I get the same error. As far as I understand the preference should be from Configuration section, then app settings file.

4. Will the best way to handle the app settings for different environments would be to only have appsettings.json and appsettings.development.json in solution and to create for all environments while provisioning azure app service (IaC template), parameter files for different environments can have the required settings (being pulled from key vault)? Or any other way is better, easy, and secure?

Answer 1

Hangfire.SqlServer 1.7.34 still uses System.Data.SqlClient instead of Microsoft.Data.SqlClient. You can however instruct it to use the Microsoft.Data namespace:

.UseSqlServerStorage(
        () => new Microsoft.Data.SqlClient.SqlConnection(builder.Configuration.GetConnectionString("DB1Context")));

See this issue and this one which refer to the piece of code above.