Azure Functions Database Connection String

Solution 1:

Jan_V almost nailed it, which led me to experiment with this in the local.settings.json

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true;",
    "AzureWebJobsDashboard": ""
  },
  "ConnectionStrings": {
    "MyConnectionString": "[YourConnectionStringHere]"
  }
}

This allows you to use the ConfigurationManager.ConnectionStrings[] we are all used to.

var sqlConnection = ConfigurationManager
                   .ConnectionStrings["MyConnectionString"].ConnectionString;

Solution 2:

The best way to do this is to add a Connection String from the Azure portal:

• From your Function App UI, click Function App Settings
• Settings / Application Settings
• Add connection strings

They will then be available using the same logic as if they were in a web.config, e.g.

var conn = System.Configuration.ConfigurationManager
                 .ConnectionStrings["MyConn"].ConnectionString;

Or if you're using a non-.NET language, you can use App Settings instead, which become simple environment variables at runtime that your functions can access.

Solution 3:

Configuration Manager will be replaced by the new Asp.Net Core Configuration System in Functions Runtime v2.

So if you are using .Net Core you should follow John Gallants Blog article: https://blog.jongallant.com/2018/01/azure-function-config/

• Works with local.settings.json and Settings in Azure Function
• Works with App Settings and Connection Strings

Solution 4:

Todd De Land's answer only works for local environment. However per this doc, published Azure Function needs connection strings be stored as app settings and retrieved by GetEnvironmentVariable.

Adding System.Configuration assembly reference is unnecessary.

string cs = Environment.GetEnvironmentVariable("MyConnectionString",EnvironmentVariableTarget.Process);

Here are the steps to make environment strings retrievable for both local and published environment

1. To support local environment, in local.settings.json, specify your connection strings inside Values node

2. To support published environment, go to portal.azure.com > your Azure Function > function node > Application Settings

3. Finally, call GetEnvironmentVariable from your Azure Function (cant get stackoverflow to display this code correctly)

Thats it.

Solution 5:

I went through several similar questions and answers here. Many of them are either misleading or assuming everybody is on the same level and understands how the azure functions are working. there is no answer for newbies like me. I would like to summarize here my solution step by step.

1. most important thing is that we understand local.settings.json file IS NOT FOR AZURE. it is to run your app in the local as the name is clearly saying. So solution is nothing to do with this file.

2. App.Config or Web.Config doesnt work for Azure function connection strings. If you have Database Layer Library you cant overwrite connection string using any of these as you would do in Asp.Net applications.

3. In order to work with, you need to define your connection string on the azure portal under the Application Settings in your Azure function. There is Connection strings. there you should copy your connection string of your DBContext. if it is edmx, it will look like as below. There is Connection type, I use it SQlAzure but I tested with Custom(somebody claimed only works with custom) works with both.

metadata=res:///Models.myDB.csdl|res:///Models.myDB.ssdl|res://*/Models.myDB.msl;provider=System.Data.SqlClient;provider connection string='data source=[yourdbURL];initial catalog=myDB;persist security info=True;user id=xxxx;password=xxx;MultipleActiveResultSets=True;App=EntityFramework

4. After you set this up, You need to read the url in your application and provide the DBContext. DbContext implements a constructor with connection string parameter. By default constructor is without any parameter but you can extend this. if you are using POCO classes, you can amend the DbContext class simply. If you use Database generated Edmx classes like I do, you don't want to touch the auto generated edmx classes, instead you want to create partial class in the same namespace and extend this class as below.

This is auto generated DbContext

namespace myApp.Data.Models
{   

    public partial class myDBEntities : DbContext
    {
        public myDBEntities()
           : base("name=myDBEntities")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

}

this is the new partial class, you create

namespace myApp.Data.Models
{
    [DbConfigurationType(typeof(myDBContextConfig))]
    partial class myDBEntities
    {

        public myDBEntities(string connectionString) : base(connectionString)
        {
        }
    }

      public  class myDBContextConfig : DbConfiguration
        {
            public myDBContextConfig()
            {
                SetProviderServices("System.Data.EntityClient", 
                SqlProviderServices.Instance);
                SetDefaultConnectionFactory(new SqlConnectionFactory());
            }
        }
    }

5. After all you can get the connection string from azure settings, in your Azure Function project with the code below and provide to your DbContext myDBEntities is the name you gave in the azure portal for your connection string.

var connString = ConfigurationManager.ConnectionStrings["myDBEntities"].ConnectionString;


 using (var dbContext = new myDBEntities(connString))
{
        //TODO:
}