Load Connection String from Config File in Azure Functions

Question

In my Azure Function I am using a Library which establishes a connection to an SQL server via the ConnectionString from the ConfigurationManager like this:

var cs = System.Configuration.ConfigurationManager.ConnectionStrings["DbConString"].ConnectionString;
DbConnection connection = new SqlConnection(cs);

Now when i set the connection string DbConString in the portal via the Application Settings everything is working fine. But for local development I use the azure-functions-cli and unfortunately I have no idea where i should place the connection string to have it loaded correctly via the ConfigurationManager.

I've tried to place it in the appsettings.json file but without success.

Edit: My appsettings.json currently looks like this:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "AzureWebJobsDashboard": "",
    "MyServiceBusReader": "Endpoint=sb://xxxx=",
    "DbConStr1": "data source=(localdb)\\MSSQLLocalDB;initial catalog=MyDb;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework",
    "ConnectionStrings": {
      "DbConStr2": "data source=(localdb)\\MS..." 
    } 
  }
}

But I am not able to access "DbConStr1" via the ConfigurationManager. Adding "DbConStr2" within "ConnectionStrings" like described here leads to a compilation error. Maybe because I am not using .NET Core?

Edit2: I messed up the nesting of "ConnectionStrings". It has to be on the same nesting level as "Values":

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "AzureWebJobsDashboard": "",
    "MyServiceBusReader": "Endpoint=sb://xxxx="
  },
  "ConnectionStrings": {
    "DbConStr": "data source=(localdb)\\MS..." 
  }
}

Answer 1

Add file local.setting.json

  {
    {
      "IsEncrypted": false,
       "Values": {
      "AzureWebJobsStorage": "UseDevelopmentStorage=true",
      "AzureWebJobsDashboard": "UseDevelopmentStorage=true",

      "tenantId": "You tenantId",
      "resource": "https://management.azure.com/",
      "ClientSecret": "You ClientSecret, Key from App Registry",
      "ClientId": "You ClientId, Application ID from App registry",

      "subscriptionId": "You subscriptionId",
      "resourceGroupName": "Your resourceGroupName",
      "serverName": " Your SQL Server",
      "databaseNameDW": "Your Database",
      "apiversion": "2017-10-01-preview"      
    }
}

In C# Code use:

private readonly static string tenantId = ConfigurationManager.AppSettings["tenantId"];

Answer 2

// C# Environment Variables example for Azure Functions v1 or v2 runtime
// This works all the way up to but not including .NET Core 2.0
var clientId = Environment.GetEnvironmentVariable("ClientId");
var clientSecret = Environment.GetEnvironmentVariable("ClientSecret");
var aadDomain = Environment.GetEnvironmentVariable("AADDomain");

Please do remember the settings you do in local.settings.json will not be reflected in azure. Please add your values in app setting from Azure portal follow the link- https://learn.microsoft.com/en-us/azure/azure-functions/functions-how-to-use-azure-function-app-settings

Answer 3

I had this same issue and am using .net standard (as opposed to core). I added my settings to the Application Settings section of my Azure function (in Azure Portal):-

I then downloaded a zip of the function:-

Included in this download is a copy of local.settings.json that includes my app settings in the correct json format. I then access them via ConfigurationManager.Appsettings["mysetting"]

Answer 4

I've found a method which feels hacky, but works: if you do evaluate Environment.GetEnvironmentVariables() you can spot that all Connection Strings from your local.settings.json are available as Environment Variables with a "ConnectionStrings:" prefix if run locally, or one from several "xxxCONNSTR_" if running on Azure, so you can define this helper function:

    private static Array ConnectionStringKeyPrefixes = new[] { "ConnectionStrings:", "SQLCONNSTR_", "SQLAZURECONNSTR_", "MYSQLCONNSTR_", "POSTGRESQLCONNSTR_", "CUSTOMCONNSTR_" };
    public static string GetConnectionStringFromSettings(string connectionStringName)
    {
        string connectionString = null;

        foreach(string prefix in ConnectionStringKeyPrefixes) {
            connectionString = Environment.GetEnvironmentVariable($"{prefix}{connectionStringName}");

            if (!string.IsNullOrWhiteSpace(connectionString))
            {
                break;
            }
        }

        return connectionString;
    }

Answer 5

The problem was, that a connection string known from e.g. a Web.config file consists of two parts:

• The connection string itself and
• the provider name.

But since the configuration file uses the JSON format it was not possible to specify both parameters.

At the time when the question was asked, it was not possible to set the provider name in the appsetings.json (now renamed to local.settings.json). But the Azure-Functions-team change this and set a default value for providerName to System.Data.SqlClient, which solved the problem.

The providerName defaults to System.Data.SqlClient. You don't have to set it manually. Just add your connection string X and read it via ConfigurationManager.ConnectionStrings["X"].

Answer 6

You should be able to manage your configuration settings with an appsettings.json file in your project structure. You can take a look here for an example of the folder structure for Azure Functions.

Additionally, this link will have some details about how to manage configuration settings with .NET Core.