EnvironmentVariablesConfigurationProvider should support all types of connectionstrings

[onybo]

When setting a connection string on an Azure Webapp using Azure CLI the type must be specified.
The following types are supported:
ApiHub, Custom, DocDb, EventHub, MySql, NotificationHub, PostgreSQL, RedisCache, SQLAzure, SQLServer, ServiceBus
az webapp config connection-string set

It seems that Microsoft.Extensions.Configuration.EnvironmentVariables.EnvironmentVariablesConfigurationProvider only support four of these: MySql, SQLAzure, SQLServer and Custom

This means that if I use Configuration.GetConnectionString("MyServiceBusConnectionString") and I have added a connection string of type ServiceBus on my Azure Website with the same name the code will unexpectedly fail. I will have to define the type of the connectionstring in azure to be Custom for this to work.

It would be much better if EnvironmentVariablesConfigurationProvider and the method AzureEnvToAppEnv supported the same types as the Azure CLI does.

These are the environment variable prefixes that I think should be added:
APIHUBCONNSTR_
DOCDBCONNSTR_
EVENTHUBCONNSTR_
NOTIFICATIONHUBCONNSTR_
POSTGRESQLCONNSTR_
REDISCACHECONNSTR_
SERVICEBUSCONNSTR_

I would be happy to make a PR for this should you agree that this is something that should be supported.

[Tratcher]

Wow, that layering is really bizarre. I'd have expected there to be a library that read from config and dealt with this, not having it embedded in the provider itself.

[poke]

Do we actually use those provider names for anything? I did a quick search across Extensions, AspNetCore, and EF Core, and outside of configuration tests, the _ProviderName config doesn’t appear to be used. What are those for?
And why would this configuration provider decide about what database provider I use? For example, it’s just by chance that MySqlConnector uses the same namespace for their MySQL provider (for migration and compatibility reasons). But they didn’t have to do it like that.

[divega]

Adding a note because we got a report via email that a customer hit the fact that we don't read the connection string correctly for PostgreSQL when it is configured in app services. If this issue was fixed, the simple code they used would work as they expect and they would get the connection string from the value configured in Azure App Services:

var cs = Configuration.GetConnectionString("MyDatabase");

Possibly the simplest workaround is to use the "custom" option instead.

To answer to previous comments:

@poke No framework code that I am aware currently uses _ProviderName , but applications can do it if the wish to. For example an application could support using different kinds of databases with the same code and resolve the DbProviderFactory from DbProviderFactories using the invariant name.

@Tratcher I am not sure I like the layering either but the logic needs to go somewhere. My main complaint is that the logic is bound to become stale anytime Azure App Services adds new connection string types. Ideally they would publish a package that encapsulates the logic. But this way we own it and it is easier for us to make it right.

@bradygaster, I was told by @glennc that you will be following up on this. I will add you to the email thread about the customer issue.

[Tratcher]

@divega can we at least look at properly re-layering this in 5.x? E.g. move the connection strings out of the environment variable provider to their own API that consumes config and is called from the templates. At the very least it would be easier to maintain and ship an out of band leaf node dependency.

[divega]

@Tratcher I think you can make a proposal, check with @bradygaster, @DamianEdwards that the resulting experience (and possible breaking change) is worth it.

[DamianEdwards]

Eeeek, having strings that are hard-coded to an Azure service's set of supported things in this provider is making me feel poorly. We shouldn't definitely look at this in 5.0

@davidfowl @glennc

[bradygaster]

absolutely agree with @DamianEdwards on this - I don't see those prefixes adding value, and have been confused by their existence for some time. I'd like to re-evaluate this, and hopefully find a way to stop doing it. IMO, a "connection string is a connection string," and shouldn't contain any "triggering behavior" for the consumer or configurator. Screen shot from the AZ CLI below for reference. Adding @btardif here from App Service team as he's on the email thread in which we're discussing this internally. @btardif is this a requirement of the web apps side or something imposed by the CLI side?

Al - is there any reason why we can't agree "a connection string is a connection string" and figure out if unwinding the prefixes is possible?

[davidfowl]

I believe the original reason for the prefix was because those got projected into the .NET Framework configuration system as provider specific connection strings.

<connectionStrings>
    <add name="ConnStringDb1" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

There's no first class concept like that in .NET Core so we mapped it internally (in a hacky way) to ConnectionString:{Name} or ConnectionString:{Name}_{Provider}. So the options here are to:

• Change AppService to match our conventions or add our conventions to the list natively when connection strings appear.
• Create an app service specific configuration provider and have users add that (or light it up on app service)
• Keep hacking and add more prefixes

[roji]

Just to mention the point discussed offline, having a provider classification on a connection string could allow us to do interesting things in the UI, e.g. a provider-specific UI for building the string, validation, or whatever. There's definitely value in that, especially for people starting out and not familiar with what options are supported in the connection string of which provider, etc.

[khellang]

Wow, this was a bizarre experience.

I created a stock "Web App with Postgres" in Azure and deployed my application, only to spend hours trying to debug why the app wasn't picking up my connection string (which had been automatically added to the web app using the "PostgreSQL" type).

That lead me to eventually find this issue and switch the connection string type to "Custom" instead, and suddenly the app worked fine. What a mess 😞

[tnc1997]

I would like to see this issue addressed if possible, or perhaps an explicit warning for those of us who select an unsupported connection string type such as PostgreSQL if that'd be easier.

[ghost]

Paging @dotnet/extensions-migration ! This issue has been revived from staleness. Please take a look and route to the appropriate repository.

[trullock]

Just spent half the day discovering this absolute stink. Please fix, or at least document.

I understand the discussion about the hack above, but right now you have half a hack. Might as well add the missing values quickly to at least have a complete hack. The refactor can happen separately

[geometrikal]

Wow, this was a bizarre experience.

I created a stock "Web App with Postgres" in Azure and deployed my application, only to spend hours trying to debug why the app wasn't picking up my connection string (which had been automatically added to the web app using the "PostgreSQL" type).

That lead me to eventually find this issue and switch the connection string type to "Custom" instead, and suddenly the app worked fine. What a mess 😞

It's mid 2022 and I ran into the same issue. Whole day wasted. Thanks finding a solution!

[tarekgh]

resurrecting the discussion to decide what best action can be taken here. Reading @davidfowl comment #36123 (comment) it looks the best action is to change the app services to support that. We already got a PR #77670 adding more support to the connection strings which we still view as a hack.

@DamianEdwards @bradygaster @davidfowl @Tratcher what you think would be the next step here to resolve this issue?

[Tratcher]

@JeremyLikness @jcjiang @ajcvickers EF is the main interested party here.

[davidfowl]

My thoughts are:

• We should make a one time breaking change and remove these from the environment variable provider.
• There should be an Azure specific configuration provider (maybe as part of the Azure SDK as a sibling to the key vault provider etc) that understands these special environment variables and turns them into connection strings.
  • This can be a dependency in your application (via an explicit call)
• We can work with the VS tooling teams and Azure teams to see ways to make this work by default

Thoughts?

[ajcvickers]

Concur with @davidfowl's suggestion.

[alfkonee]

I also agree with this path here

My thoughts are:

• We should make a one time breaking change and remove these from the environment variable provider.

• There should be an Azure specific configuration provider (maybe as part of the Azure SDK as a sibling to the key vault provider etc) that understands these special environment variables and turns them into connection strings.

  • This can be a dependency in your application (via an explicit call)

• We can work with the VS tooling teams and Azure teams to see ways to make this work by default

Thoughts?

But a few questions on when the Azure Specific Provider may become available? if it's going to take some unknow large amount of time to get ball rolling on the Azure Specific, then I think we should allow the extra fixes in the Current Environment Variable Provider while we wait for the Azure Team to align on their Provider. Then once that is ready for use we can then Update Docs and deprecate the Azure Based Implementations in the Base Env Provider

[davidfowl]

I don’t think so. We’re past .NET 7 anyways so this is slated for .NET 8. The azure provider can be part of the azure sdk or an app service sdk and not wait for a .NET ship cycle. I’ll start that discussion with the relevant teams.

[tarekgh]

@davidfowl I'll keep this issue to track the removal of the hack from the extension libraries when things ready. Let's start talking to the Azure/app services guys to execute the plan.

[nschoenberg]

I tryed 3 hours to figure out why my postgre sql connection string has not been read from app settings.
In my docker logs i found following hint:
An error occurred using the connection to database '' on server ''

After a lot of research I found this issue and the workaround of @divega worked for me, changing the type of connection string from "PostgreSQL" to "Custom"

[layomia]

@davidfowl we didn't get around to working on this. Given the time left for 8.0 dev, okay to move this to future?

[tarekgh]

Moving this to next release and @davidfowl can bring it back if there is a chance be done in 8.0.

[albertodall]

Wow, this was a bizarre experience.
I created a stock "Web App with Postgres" in Azure and deployed my application, only to spend hours trying to debug why the app wasn't picking up my connection string (which had been automatically added to the web app using the "PostgreSQL" type).
That lead me to eventually find this issue and switch the connection string type to "Custom" instead, and suddenly the app worked fine. What a mess 😞

It's mid 2022 and I ran into the same issue. Whole day wasted. Thanks finding a solution!

I did the same thing today. Frustrating!

[alsmyr]

Took us a while to figure this out. VSCode suggests ServiceBus as a totally valid connectionString type for my template. And then the provider refuse to provide it in the function.

[pk-ot]

Fact: The auto-generated (semantically correct) configuration by the Azure Web App wizard that creates a Postgres database and web app in one step is just not working.

Why the template for that wizard is not simply forcing a type of "Custom" for the connection string and adds a short note somewhere on this known issue, is beyond me. Either nobody of the responsibles has ever tested this with even just a "hello world"-type of application, or they have and simply don't care. Microsoft rather lets everyone figure it out by themselves (since 2019!). Losing hours to a problem like this is frustrating.

But hey, now it's scheduled for .NET 9. 🙏

[sqbiak]

Hello from a 2024.... from a S&P 500 where I wanted to fire a guy for this code:

        string connectionString = null;
        IDictionary environmentVariables = Environment.GetEnvironmentVariables();
        foreach (DictionaryEntry envVar in environmentVariables)
        {
            if (envVar.Key.ToString().IndexOf("POSTGRESQLCONNSTR_x", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                connectionString = envVar.Value.ToString();
                break;
            }
        }

but then a simple

         builder.Services.AddDbContext<DatabaseContext>(options =>
         {
             var connectionString = builder.Configuration.GetConnectionString("POSTGRESQLCONNSTR_x");
             if (string.IsNullOrEmpty(connectionString))
             {
                 throw new InvalidOperationException("Database connection string 'POSTGRESQLCONNSTR_x' not found.");
             }
             options.UseNpgsql(connectionString);
         }); 

didn't work.

[am11]

var connectionString = builder.Configuration.GetConnectionString("POSTGRESQLCONNSTR_x");

nit: with GetConnectionString() API, it should be just "x"; the full form"POSTGRESQLCONNSTR_x" can be used with indexer ([]).

Meanwhile, we can inherit from EnvironmentVariablesConfigurationProvider and add the missing prefixes.

Source

using System;
using System.Collections;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.EnvironmentVariables;

internal static class CustomEnvironmentVariablesExtensions
{
    public static IConfigurationBuilder AddCustomEnvironmentVariables(this IConfigurationBuilder builder, string? prefix = null)
    {
        return builder.Add(new CustomEnvironmentVariablesConfigurationProvider.Source(prefix));
    }
}

file class CustomEnvironmentVariablesConfigurationProvider(string? prefix) :
    EnvironmentVariablesConfigurationProvider(prefix)
{
    private const string PostgreSqlPrefix = "POSTGRESQLCONNSTR_";
    private const string NotificationHubPrefix = "NOTIFICATIONHUBCONNSTR_";
    private const string ServiceBusPrefix = "SERVICEBUSCONNSTR_";
    private const string EventHubPrefix = "EVENTHUBCONNSTR_";
    private const string DocumentDbPrefix = "DOCDBCONNSTR_";
    private const string RedisCachePrefix = "REDISCACHECONNSTR_";

    public override void Load()
    {
        base.Load();

        LoadAdditionalPrefixes(Environment.GetEnvironmentVariables());
    }

    private void LoadAdditionalPrefixes(IDictionary envVariables)
    {
        foreach (DictionaryEntry entry in envVariables)
        {
            string key = (string)entry.Key;
            string? value = (string?)entry.Value;

            if (key.StartsWith(PostgreSqlPrefix, StringComparison.OrdinalIgnoreCase))
            {
                HandleConnectionStringPrefix(PostgreSqlPrefix, key, value, "Npgsql");
            }
            else if (key.StartsWith(NotificationHubPrefix, StringComparison.OrdinalIgnoreCase))
            {
                HandleConnectionStringPrefix(NotificationHubPrefix, key, value);
            }
            else if (key.StartsWith(ServiceBusPrefix, StringComparison.OrdinalIgnoreCase))
            {
                HandleConnectionStringPrefix(ServiceBusPrefix, key, value);
            }
            else if (key.StartsWith(EventHubPrefix, StringComparison.OrdinalIgnoreCase))
            {
                HandleConnectionStringPrefix(EventHubPrefix, key, value);
            }
            else if (key.StartsWith(DocumentDbPrefix, StringComparison.OrdinalIgnoreCase))
            {
                HandleConnectionStringPrefix(DocumentDbPrefix, key, value);
            }
            else if (key.StartsWith(RedisCachePrefix, StringComparison.OrdinalIgnoreCase))
            {
                HandleConnectionStringPrefix(RedisCachePrefix, key, value);
            }
        }
    }

    private void HandleConnectionStringPrefix(string prefix, string fullKey, string? value, string? provider = null)
    {
        string normalizedKey = Normalize(fullKey.Substring(prefix.Length));

        Data[$"ConnectionStrings:{normalizedKey}"] = value;

        if (provider is not null)
        {
            Data[$"ConnectionStrings:{normalizedKey}_ProviderName"] = provider;
        }
    }

    private static string Normalize(string key) => key.Replace("__", ConfigurationPath.KeyDelimiter);

    internal class Source(string? prefix) : IConfigurationSource
    {
        public IConfigurationProvider Build(IConfigurationBuilder builder)
        {
            return new CustomEnvironmentVariablesConfigurationProvider(prefix);
        }
    }

}

usage:

using System;
using Microsoft.Extensions.Configuration;

IConfigurationRoot configuration = new ConfigurationBuilder().AddCustomEnvironmentVariables().Build();

Console.WriteLine($"PostgreSQL Connection String with GetConnectionString: {configuration.GetConnectionString("x")}");
Console.WriteLine($"PostgreSQL Connection String with indexer: {configuration["POSTGRESQLCONNSTR_x"]}");

run with dotnet 9.0:

$ export POSTGRESQLCONNSTR_x="Server=myServer;Database=myDb;User=myUser;Password=myPass;"
$ dotnet add package Microsoft.Extensions.Configuration.EnvironmentVariables
$ dotnet run
PostgreSQL Connection String with GetConnectionString: Server=myServer;Database=myDb;User=myUser;Password=myPass;
PostgreSQL Connection String with indexer: Server=myServer;Database=myDb;User=myUser;Password=myPass;