How to Set Separate Database Connection Strings for Azure App Service Deployment Slots

I have an ASP.NET Core app deployed in Azure App Service, with both staging and production deployment slots. Currently, the app uses a single Azure SQL Database connection string stored in Azure Key Vault.

In my Program.cs file, I retrieve the connection string as follows:

var sqlConnectionString = builder.Configuration.GetSection("sqlConnectionString").Value;

builder.Services.AddDbContext<MyAppContext>(options =>
    options.UseSqlServer(sqlConnectionString ?? throw new InvalidOperationException("Connection string 'MyAppContext' not found.")));

The sqlConnectionString is not stored in appsettings.json; it is fetched directly from the Key Vault.

I need to connect the production deployment slot to a different Azure SQL Database. This should ensure that when I swap slots, the production instance of the app connects to the production database.

I added a new Connection String named sqlConnectionString of type SQLAzure under the Environment Variables in the production slot settings. However, this did not work, and the production slot still connects to the staging database.

In Program.cs I added the following code:

var isDevelopment = builder.Environment.IsDevelopment();
var sqlConnectionString = "";

if (isDevelopment)
{
    sqlConnectionString = builder.Configuration.GetSection("sqlConnectionString").Value;
}
else
{
    sqlConnectionString = builder.Configuration.GetConnectionString("sqlConnectionString");
}

In development mode, I get the connection string from Key Vault using the GetSection method. In staging and production modes, I get the connection string using the GetConnectionString method.

Additionally, I made the following improvements in Azure App Service:

1. I added the sqlConnectionString to the Connection Strings section for both the Production and Staging deployment slots.
2. I added an environment variable named ASPNETCORE_ENVIRONMENT in both the staging and production environments. For the staging environment, the value is set to Staging, and for the production environment, the value is set to Production.

As a result, my development and staging environments are now connected to one database, while the production environment is connected to a separate production database.