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:
Azure/azure-functions-core-tools#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: MicrosoftDocs/azure-docs#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.

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%

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

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 %..%.