Document use of connection strings in local.settings.json

[mattchenderson]

We've seen confusion when folks try to map the Connection Strings feature in the portal to local. Local connection strings should just be treated like any other app setting. Our docs should explain this.

[nzthiago]

I want to add some more context to this.

Say I create a value in local.settings.json called "MyConnString". When debugging locally a C# class library in both v1 and v2, you can get to that value from using:

Environment.GetEnvironmentVariable("MyConnString"); 

After deploying these C# compiled class libraries, that line of code doesn't work. It instead behaves the same way as the non .NET languages, where prefixes are added to each type of connection string that can be created in App Settings.

I.e., if I create a connection string in the connection string section of app settings for my Function v1 or V2 on Azure called “MyConnString” I can retrieve it with the following:

“Environment.GetEnvironmentVariable("SQLAZURECONNSTR_MyConnString");”

This is confusing since there’s no concept of connection strings in local.settings.json and when testing locally you need to either remember to create a Value that has the entire prefix as part of it, or cater in the code to try getting both MyConnString and SQLAZURECONNSTR_MyConnString from environment variables.

To add to the confusion, there's also this bug in core tools, discussing how the behavior of V1 and V2 will be different in .NET functions where you can't use ConfigurationManager anymore in V2: https://github.com/Azure/azure-functions-core-tools/issues/328

So I believe there needs to be a review and documentation of:

• Why is there a "connection strings" section in App Settings for Functions (and App Service overall)
• What is the best practice for storing and retrieving connection strings from App Settings for Functions (of any kind).
• is there a recommended approach for storing and accessing secure string settings within an Azure Function?
• Are App Settings backed by Key Vault already and 'Connection Strings' are there as a remnant from older App Service features, and we should just go with App Settings from here on?

[ggailey777]

@nzthiago just to clarify, we are discussing the "ConnectionStrings" object in the local.settings.json (since "Values" just maps to so called application settings)?

{
  "IsEncrypted": false,   
  "Values": {
	"AzureWebJobsStorage": "<connection string>", 
	"AzureWebJobsDashboard": "<connection string>" 
  },
  "ConnectionStrings": {
	"SQLConnectionString": "Value"
  }
}

[GuyHarwood]

i have just created a v2 azure function in javascript with the docker template, and have wired up my function trigger to blob storage like so...

  "disabled": false,
  "bindings": [
    {
      "name": "censusFile",
      "type": "blobTrigger",
      "direction": "in",
      "path": "csvupload/{name}.csv",
      "connection": "InboundStorageAccount"
    },
    {
      "tableName": "censusImport",
      "connection": "OutboundStorageAccount",
      "name": "outputTable",
      "type": "table",
      "direction": "out"
    }
  ]
}

in local.settings.json i have the following....

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "DefaultEndpointsProtocol=https;AccountName=xxx;AccountKey=yyy==;",
    "InboundStorageAccount": "DefaultEndpointsProtocol=https;AccountName=xxx;AccountKey=yyy==;",
    "OutboundStorageAccount": "DefaultEndpointsProtocol=https;AccountName=xxx;AccountKey=yyy==;"
  }
}

When i deploy to a docker based azure function in azure the local.settings.json is not present (as its excluded from git, for obvious reasons). Would i be correct in assuming i just add the 3 settings (AzureWebJobsStorage, InboundStorageAccount, OutboundStorageAccount) to the Application Settings blade?

If not, how do i initialise these required values? i've seen examples that do it in the Dockerfile, but that means setting them when the container is built, rather than in the target environment, which is awkward.

I have tried adding them, but the function never triggers. Application insights appears to be broken, and i'm getting no output from az webapp log tail.

its like a black box!

[benmccallum]

I'm not getting the exact same issue as above I don't believe, but I do think the docs could be worded better as doing Environment.GetEnvironmentVariable("SlackWebhookUrl", EnvironmentVariableTarget.Process) like the docs suggests gets a setting in local.settings.json at { "Values": { "SlackWebhookUrl": "xyz" } }, so one level down in Values than one would expect. The docs don't have the settings json to make that evident.

Logged a PR: https://github.com/MicrosoftDocs/azure-docs/pull/9047

[ggailey777]

I have an update that attempts to address the ConnectionStrings issue in the private docs repo: https://github.com/MicrosoftDocs/azure-docs-pr/pull/43131

[johnwc]

The connection string in the local.settings.json does indeed work, but it has a different naming convention than what is used in a Azure Functions App Service. This is the code that I use to retrieve them both local and in a app service.

public static string GetSqlConnectionString(string name)
{
    string conStr = System.Environment.GetEnvironmentVariable($"ConnectionStrings:{name}", EnvironmentVariableTarget.Process);
    if (string.IsNullOrEmpty(conStr)) // Azure Functions App Service naming convention
        conStr = System.Environment.GetEnvironmentVariable($"SQLCONNSTR_{name}", EnvironmentVariableTarget.Process);
    return conStr;
}
public static string GetSqlAzureConnectionString(string name)
{
    string conStr = System.Environment.GetEnvironmentVariable($"ConnectionStrings:{name}", EnvironmentVariableTarget.Process);
    if (string.IsNullOrEmpty(conStr)) // Azure Functions App Service naming convention
        conStr = System.Environment.GetEnvironmentVariable($"SQLAZURECONNSTR_{name}", EnvironmentVariableTarget.Process);
    return conStr;
}
public static string GetMySqlConnectionString(string name)
{
    string conStr = System.Environment.GetEnvironmentVariable($"ConnectionStrings:{name}", EnvironmentVariableTarget.Process);
    if (string.IsNullOrEmpty(conStr)) // Azure Functions App Service naming convention
        conStr = System.Environment.GetEnvironmentVariable($"MYSQLCONNSTR_{name}", EnvironmentVariableTarget.Process);
    return conStr;
}
public static string GetCustomConnectionString(string name)
{
    string conStr = System.Environment.GetEnvironmentVariable($"ConnectionStrings:{name}", EnvironmentVariableTarget.Process);
    if (string.IsNullOrEmpty(conStr)) // Azure Functions App Service naming convention
        conStr = System.Environment.GetEnvironmentVariable($"CUSTOMCONNSTR_{name}", EnvironmentVariableTarget.Process);
    return conStr;
}

[brandonh-msft]

Additionally when we fix this we need to think about Bindings that reference Connection String Settings values. Getting conn strings from the Connection Strings area of App Service settings via code is one thing, but the Bindings need to get them properly as well.

[arcotek-ltd]

I'd like to chip in here as I am finding it hard to fathom this simple task.

Background

First off, my connection string refers to Azure storage and not SQL. At this point in time, I am working locally with Visual Studio 2017.

I am using BlobTrigger and I am trying to parameterise the container ('intray' in the example):

public static void Run([BlobTrigger(blobPath: "intray/{name}", Connection = "AzureWebJobsStorage")]Stream myBlob, string name, ILogger log)
 {
    ...
 }

If I replace "intray" with "{container}/{name}" or use any form of variable substitution, I get the error:

[20/08/2018 14:56:36] Run: Microsoft.Azure.WebJobs.Host: Error indexing method 'TriggerLogicApp.Run'. Microsoft.Azure.WebJobs.Host: Invalid blob trigger path '{container}/{name}'. Container paths cannot contain {resolve} tokens.

So, I am looking at local.settings.json in detail as this seems to be the place to set the name of the container.

Issue 1

According to your docs, local.settings.json maps to function.json (when compiled), however, although you mention local.settings.json, it looks very different to function.json.

local.settings.json

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "myConnectionStringCopiedFromAzurePortal",
    "AzureWebJobsDashboard": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet"
  }
}

Example function.json

    ...
    {
      "name": "imageSmall",
      "type": "blob",
      "path": "sample-images-sm/{filename}",
      "direction": "out",
      "connection": "MyStorageConnection"
    }
  ],
}

Therefore can you provide some examples of what a local.settings.json should look like when it contains name, type, path, direction and connection? I assume connection would be the same value as used in AzureWebJobsStorage, but this isn't clear.

Issue 2

How does one reference the setting in the code?

public static void Run([BlobTrigger(blobPath: "intray/{name}", Connection = "AzureWebJobsStorage")]Stream myBlob, string name, ILogger log)
 {
    ...
 }

blobPath: doesn't seem to like anything other than "container/{name}" and I can't find any example of how to use something other than that. In your 4th code example:

[FunctionName("ResizeImage")]
public static void Run(
    [BlobTrigger("sample-images/{filename}")] Stream image,
    [Blob("sample-images-sm/{filename}", FileAccess.Write)] Stream imageSmall,
    string filename,
    TraceWriter log)
{
    log.Info($"Blob trigger processing: {filename}");
    // ...
}

you are hard coding the container' name (sample-images). How do I refer to the container name given in path from function.json, as I can't use Environment.GetEnvironmentVariable() here?

Thanks

[brandonh-msft]

@arcotek-ltd : BlobTrigger can only listen to one specific container. Not multiple containers. If you wish to do this, use Azure Event Grid Storage Events on a Storage Account (bonus: it's faster)

local.settings.json does map to Application Settings. function.json != application settings; it's the definition of the functions contained within your function app. Very different from the settings for those function apps

If you wanted to combine these - define the container in your app settings - you reference an app setting in a binding parameter by using %appsettingname%

[arcotek-ltd]

BlobTrigger can only listen to one specific container. Not multiple containers. If you wish to do this, use Azure Event Grid Storage Events on a Storage Account (it's faster, anyway ;))

I only want to work with one container, but I don't want to hard code it. Forgive me if I don't know as much as you, but from reading your documents, when testing with Visual Studio, I should be able to put the container' name "path" : "container/{name} in local.settings.json, but where / how? My local.settings.json looks very different to the {"bindings": [ {..}]} examples. How do they relate?

All your examples seem to show ..[BlobTrigger("sample-images/{filename}")].. and to my ignorant eye, that looks like it's hard coded. How do I call / reference / replace "sample-images/{filename}" with valueFromLocal.Setting.Json?

If I change the name in the code to ..[BlobTrigger("sample-images/{filename}")].. to ..[BlobTrigger("somecontainer/{filename}")].. and add path to local.settings.json:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "myConnectionStringCopiedFromAzurePortal",
    "AzureWebJobsDashboard": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "path": "intray/{name}"
  }
}

When running locally, I get Skipping 'path' from local settings as it's already defined in current environment variables. and looking at function.json, I see:

{
  "generatedBy": "Microsoft.NET.Sdk.Functions-1.0.14",
  "configurationSource": "attributes",
  "bindings": [
    {
      "type": "blobTrigger",
      "connection": "AzureWebJobsStorage",
      "path": "container/{filename}",
      "name": "myBlob"
    }
  ],
  "disabled": false,
  "scriptFile": "../bin/LogicAppTriggerFunction.dll",
  "entryPoint": "LogicAppTriggerFunction.TriggerLogicApp.Run"
}

I am sorry if my little brain has got confused between app settings and function.json. I'll update above, nevertheless, I cannot see in your docs how "container/{name}" is referenced in the code.

[brandonh-msft]

@arcotek-ltd

the container/{name} is referenced in your trigger implementation as shown here:

[FunctionName("BlobTriggerCSharp")]        
public static void Run([BlobTrigger("samples-workitems/{name}")] Stream myBlob, string name, TraceWriter log)
{
    log.Info($"C# Blob trigger function Processed blob\n Name:{name} \n Size: {myBlob.Length} Bytes");
}

where container is samples-workitems

back to your original post, though, you can only parameterize the container via app settings (eg: %container-to-watch%), you cannot subscribe to multiple containers in a storage account with BlobTrigger

[arcotek-ltd]

For anyone else with the same issue, what the docs fail to state (that I could find) is answered here.

So, in brief:

1. Create a new entry in local.settings.json: "blobContainerName":"intray"
2. Change the line public static void Run([BlobTrigger("samples-workitems/{name}", Connection = "AzureWebJobsStorage")]Stream myBlob, string name, ILogger log) { to public static void Run([BlobTrigger("%blobContainerName%/{name}", Connection = "AzureWebJobsStorage")]Stream myBlob, string name, ILogger log) {

The %..% will read variables from local.settings.json and add them to function.json at compile time.

I suspect what I was trying to do was so very simple I ended up confusing @brandonh-msft, but we all have to start learning somewhere.

[paulbatum]

Hmm.. the %..% will not add the resolved variables to function.json at compile time. Its resolved at runtime. You can confirm this yourself by checking your function.json after doing a build - you should see the function.json will also contain the %..%.

[ColbyTresness]

@mattchenderson is this resolved now? Or still open?

[brandonh-msft]

IMO still open.

This area talks about using "ConnectionStrings" in local.settings.json while this area provides no guidance on how to get those back out as @nzthiago alluded to.

[johnwc]

I think the path this took to conversate about bindings is out of scope for what this issue was open for. I think it may be best to move all this binding issue into a new issue.

The local and remote(Azure) method for reading connection strings from environment variables needs to match, or the docs need to be updated to clearly state it works differently in Azure vs local.

[jwisener]

This is so very confusing. We have many .net core applications across many environments. We are starting to look at azure functions. So are you basically saying, that I have to go into each environment (dev, test, staging, production) and set hundreds of key values via the portal by hand? Some keys we would set via the portal as they are sensitive but not all of them, for example a 3rd party endpoint for which we need to consume data. I am really having a hard time finding/following any documentation on the proper way to support multiple environments, coming from .net core this is all straight forward with how appsettings.json files work.

[johnwc]

@jwisener If my memory serves me correctly... When you publish a functions project into Azure, it also publishes your local.settings.json settings. You'd then just update any differences needed within Azure, like database connection string pointing to correct server for the environment. Once your settings are in Azure though, I do not think a publish will override them, only add the new/missing settings. (You will need to verify that though) This would be the best practice to follow, utilizing maybe a PowerShell script to automate the updating of the Azure Function Service App settings.

But... If this is too much of a task, because you are deploying to many many environments. You can always utilize a Azure Key Vault to store your config settings AKA secrets. Then, in your function app, read your settings from the key vault. You would create publish profiles that would then use different Configuration names. Inside your code you could then do conditional compilation to set the deployed environment, based on the profile that was used to publish.

Project Configurations

Old	New
Release	Prod
Debug	Debug
	UAT
	Staging

Don't forget to set the conditional compilation symbols under project settings for each Configuration

namespace App.Functions
{
    public class Jobs
    {
#if PROD
        const string Env = "PROD";
#elif UAT
        const string Env = "UAT";
#elif STAGING
        const string Env = "STAGING";
#else
        const string Env = "LOCAL";
#endif
        // Value stored in local.settings.json, gets published to Azure Ex: https://MyVault-{0}.vault.azure.net
        public readonly string KeyVaultUrlFormat = Config.GetSetting("KeyVaultUrl");
        ...
        [FunctionName("WorkingHard")]
        public async static Task Run(
            [TimerTrigger("0 0 0 * * *")]TimerInfo myTimer,
            ILogger log,
            CancellationToken cancellationToken)
        {
            // Use "Env" constant to read from correct KeyVault
            AzureServiceTokenProvider azureServiceTokenProvider = new AzureServiceTokenProvider();

            var keyVaultClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback));
            var keyVault = string.Format(this.KeyVaultUrlFormat, this.Env);
            var keyVaultKeyss = await keyVaultClient.GetSecretsAsync(keyVault);
            ...
        }
    }
}

Note: Config.GetSetting is nothing more than a static class using the methods I posted earlier in this comment

[Kegulf]

The connection string in the local.settings.json does indeed work, but it has a different naming convention than what is used in a Azure Functions App Service. This is the code that I use to retrieve them both local and in a app service.

public static string GetSqlConnectionString(string name)
{
    string conStr = System.Environment.GetEnvironmentVariable($"ConnectionStrings:{name}", EnvironmentVariableTarget.Process);
    if (string.IsNullOrEmpty(conStr)) // Azure Functions App Service naming convention
        conStr = System.Environment.GetEnvironmentVariable($"SQLCONNSTR_{name}", EnvironmentVariableTarget.Process);
    return conStr;
}
public static string GetSqlAzureConnectionString(string name)
{
    string conStr = System.Environment.GetEnvironmentVariable($"ConnectionStrings:{name}", EnvironmentVariableTarget.Process);
    if (string.IsNullOrEmpty(conStr)) // Azure Functions App Service naming convention
        conStr = System.Environment.GetEnvironmentVariable($"SQLAZURECONNSTR_{name}", EnvironmentVariableTarget.Process);
    return conStr;
}
public static string GetMySqlConnectionString(string name)
{
    string conStr = System.Environment.GetEnvironmentVariable($"ConnectionStrings:{name}", EnvironmentVariableTarget.Process);
    if (string.IsNullOrEmpty(conStr)) // Azure Functions App Service naming convention
        conStr = System.Environment.GetEnvironmentVariable($"MYSQLCONNSTR_{name}", EnvironmentVariableTarget.Process);
    return conStr;
}
public static string GetCustomConnectionString(string name)
{
    string conStr = System.Environment.GetEnvironmentVariable($"ConnectionStrings:{name}", EnvironmentVariableTarget.Process);
    if (string.IsNullOrEmpty(conStr)) // Azure Functions App Service naming convention
        conStr = System.Environment.GetEnvironmentVariable($"CUSTOMCONNSTR_{name}", EnvironmentVariableTarget.Process);
    return conStr;
}

This worked! ^ ( Functions v2 and ASP.NET Core 2.1.0 )

THANK YOU! <3 Havent seen the prefixes referenced anywhere else.. If there is a docs page for this it should be added as a reference in the Functions DI docs page!

I stumbled over this page by accident!

[brandonh-msft]

@jeffhollan who could we ping on Docs side to start on a page for some content outlining what @johnwc showed?

cc @mcollier

[jeffhollan]

I think one challenge I have is that I don't think I would recommend ever using the ConnectionString to store my connection strings unless I'm using a library that requires it (e.g. Entity Framework I believe? which adds the prefixes for you). I understand why people do it (because it says 'connection strings' and they have a connection string so makes sense to put there), but for now we recommend people don't use connection strings for strings they want to access and environment variables. I'm open to the fact that I may be missing something - but I wonder if the right option is we remove or warn against using this section of local.settings.json?

[jeffhollan]

Or put differently - is there any reason we should just have people put these all in "values" and then they don't have to worry about know what prefix to use?

[brandonh-msft]

Totally get what you're saying; something like "This section is only intended for use by data libraries like Entity Framework. Store your own custom connection strings in the values area"?

[jwisener]

We have libraries we use in .net core api projects, console apps and now in addition to azure functions. It’s it shame they are so different. Right now we have two different formats we have to maintain. @jeffhollan it would be better if the approaches were unified so settings worked the same.

[jwisener]

Or to say it another way, why can't the approaches be done consistently?

[nzthiago]

@brandonh-msft that indeed what we added to the ConnectionStrings part of the table in the link that @jeffhollan pointed out, that was updated as part of this thread I think.

[brandonh-msft]

ah yes i should've clicked thru. 👍🏻👍🏻

[kirajhpang]

I think one challenge I have is that I don't think I would recommend ever using the ConnectionString to store my connection strings unless I'm using a library that requires it (e.g. Entity Framework I believe? which adds the prefixes for you). I understand why people do it (because it says 'connection strings' and they have a connection string so makes sense to put there), but for now we recommend people don't use connection strings for strings they want to access and environment variables. I'm open to the fact that I may be missing something - but I wonder if the right option is we remove or warn against using this section of local.settings.json?

I tot I am the only one who put connection string under Application Settings section in Azure Function, apparently this is one of the suggestion method.

[ggailey777]

I worked with @nzthiago to try and tease out the nuance between Application Settings and Connection Strings in the local.settings.json section—clearly, unsuccessfully. 😦

Another complicating factor is that we have an inconsistency between the portal UI and local.settings.json:

Portal	local.settings.json
Application Settings	Values
Connection Strings	ConnectionStrings

We can remove the discussion of ConnectionStrings from local.settings.json on the assumption that no one should be using it, thus reducing confusion. However, this doesn't address the potential confusion due to Connection Strings in the portal.

[grahambunce]

Frankly (and yes this is a rant that doesn't help at all but it gets it off my chest).... why did you decide that Azure Functions should break years worth of learned behaviour of how appsettings and configuration settings are separated and then put them into a single "values" list UNLESS its for SQL Server/EF/Similar??

I suggest somebody reads this: https://en.wikipedia.org/wiki/Principle_of_least_astonishment

Why on earth did you not just keep to the learned behaviour of "connection strings go in a connection strings section, app settings go in a connection strings section and anything else can go in Value or a custom section such as Host" ??? This problem/misunderstanding would never have occured.