CODEWITHSUNDEEP
c#azureazure-functionsazure-sql-database
Lereveme
Lereveme

Reputation: 1674

Azure Functions Database Connection String

How do I add or access an app.config file in Azure functions to add a database connection string?

If you're not supposed to add an app.config and there is a better way to access an external database to execute the function please let me know best practices. Thanks!

Upvotes: 93

Views: 112502

Answers (16)

Shashank Lokanadham
Shashank Lokanadham

Reputation: 11

Having the Connection details in the Key vault and accessing the connection string using the User Assigned Managed Identity is the best approach to connect to the Database.

follow below steps to achieve this.

  1. Give the Azure Function User 'Key-Vault-Contributor' role.

  2. Add the AZURE_CLIENT_ID in the Configuration as shown below. Configuration of Azure Function

  3. add the below lines to your code. credential = DefaultAzureCredential(managed_identity_client_id=client_id) keyvault_client = SecretClient(vault_url=key_vault_url, credential=credential) Replace 'key_vault_url' with your key vault URL.

  4. Now you can use the keyvault_client to get the secret values using below syntax. secret_variable = keyvault_client.get_secret("secret-name")

    Replace the Now 'secret-name' with the secret name which you created in azure KeyVault. secret_variable.value can be used in your code where you want to use it for connection.

Upvotes: 0

Greg Gum
Greg Gum

Reputation: 37875

Azure Functions Version 4: How to configure your connection string.

Concepts:

A. There are four versions of Azure Functions. The below was written for version 4. Edit your project file if you are unsure of which version you are using. <AzureFunctionsVersion>v4</AzureFunctionsVersion>

B. You must configure both the local dev environment, and the server environment separately. They are different and require different configuration, but the code which uses the configuration will be the same.

C. Use environmental variables. The usual appconfig.json file is not supported in Azure Functions, being a lightweight execution environment. (You could also use AzureKey vault but that is a different topic.)

  1. To set your dev environment variable, add it to local.settings.json Note that this file is NOT published, and applies only to your local machine as the name implies.

    { "IsEncrypted": false, "Values": { "AzureWebJobsStorage": "UseDevelopmentStorage=true", "FUNCTIONS_WORKER_RUNTIME": "dotnet", "DefaultConnectionString": "MyConnectionString" } }

  2. For your published environment, go into the Azure Portal and set it there.

Upvotes: 1

Chamika Sandamal
Chamika Sandamal

Reputation: 24292

If you are using function runtime v3, then following approach will work for you.

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

In your function's startup file

public override void ConfigureAppConfiguration(IFunctionsConfigurationBuilder builder)
{
    var config = builder.ConfigurationBuilder.Build();
    var connectionString = config.GetConnectionString("MyConnectionString");
}

Upvotes: 4

Ogglas
Ogglas

Reputation: 69928

Addition to the answer from @ToddDeLand.

With a local.settings.json like this:

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

You can then access your connection string like this, no NuGets needed.

var connectionString = Environment.GetEnvironmentVariable("ConnectionStrings:MyConnectionString");

Microsoft recommends this approach here:

https://learn.microsoft.com/en-us/azure/azure-functions/functions-dotnet-class-library#environment-variables

If you add the connection string to values:

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

You can access your connection string like this:

var connectionString = Environment.GetEnvironmentVariable("MyConnectionString");

Sources:

https://stackoverflow.com/a/52219491/3850405

https://github.com/Azure/Azure-Functions/issues/717#issuecomment-400098791

Upvotes: 19

bhargav bandhakavi
bhargav bandhakavi

Reputation: 11

The Best way to handle connection strings is by using "Azure Key Vault". You can store all the important secrets in Key Vault and consume in the Application. As suggested by other members you can use Application Settings.

help full links: https://learn.microsoft.com/en-us/azure-stack/user/azure-stack-key-vault-manage-portal?view=azs-2002

https://learn.microsoft.com/en-us/aspnet/core/security/key-vault-configuration?view=aspnetcore-3.1

Upvotes: 1

Vijay Koganti
Vijay Koganti

Reputation: 21

some of the above suggestions work. However there is a more straight forward way of setting a connection string. It is by using the 'publish' screen one sees after hitting the publish setting. see picture from documentation here enter image description here

Upvotes: 2

Ahsan Raza
Ahsan Raza

Reputation: 141

Try this method.

public static string GetConnectionString(string name)
{
    string conStr = System.Environment.GetEnvironmentVariable($"ConnectionStrings:{name}", 
                    EnvironmentVariableTarget.Process);

    // Azure Functions App Service naming 
    if (string.IsNullOrEmpty(conStr))convention
        conStr = System.Environment.GetEnvironmentVariable($"SQLAZURECONNSTR_{name}", 
                 EnvironmentVariableTarget.Process);

    return conStr;
}

Upvotes: 2

Md Farid Uddin Kiron
Md Farid Uddin Kiron

Reputation: 22419

I have tried below code snippet on my local database that seems easy. Let's have a look.

Nuget Extention:

Download following reference from Nuget Package Manager On your project Dependencies part

using System.Data.SqlClient;

local.settings.json:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "sqldb_connection": "Data Source=.;Initial Catalog=DatabaseName;Connection Timeout=30;Integrated Security=True;"
  }
}

Read Connection On Function Body:

//Read database Connection 

        var sqlConnection = Environment.GetEnvironmentVariable("sqldb_connection");

Function Read Write Operation Using Connection String:

// Convert all request perameter into Json object

                var content = req.Content;
                string jsonContent = content.ReadAsStringAsync().Result;
                dynamic requestPram = JsonConvert.DeserializeObject<AzureSqlTableClass>(jsonContent);

                // Validate required param

                if (string.IsNullOrEmpty(requestPram.FirstName))
                {
                    return req.CreateResponse(HttpStatusCode.OK, "Please enter First Name!");
                }
                if (string.IsNullOrEmpty(requestPram.LastName))
                {
                    return req.CreateResponse(HttpStatusCode.OK, "Please enter Last Name!");
                }



                //Read database Connection

                var sqlConnection = Environment.GetEnvironmentVariable("sqldb_connection");
                var responseResults = 0;

                //Read Write Uisng Connection String

                using (SqlConnection conn = new SqlConnection(sqlConnection))
                {
                    conn.Open();
                    var text = "INSERT INTO AzureSqlTable VALUES ('" + requestPram.FirstName + "', '" + requestPram.LastName + "', '" + requestPram.Email + "') ";

                    using (SqlCommand cmd = new SqlCommand(text, conn))
                    {
                        responseResults = await cmd.ExecuteNonQueryAsync();
                    }
                    conn.Close();
                }

                return req.CreateResponse(HttpStatusCode.OK, responseResults);

Note: While publish your function on azure portal just replace the connection string on local.settings.json file. It will work accordingly. See the screen shot below:

enter image description here

Upvotes: 2

Agrawal Shraddha
Agrawal Shraddha

Reputation: 764

You should store connection string in azure key vault and enable MSI on azure function and add access policy on azure key vault to read key value.

Upvotes: 0

ManiVI
ManiVI

Reputation: 592

Below worked for me both locally & in Azure for an http trigger function that queries cosmos db

added Microsoft.Azure.WebJobs.Extensions.CosmosDB nuget package reference to project

connection string settings:

local.settings.json

{
  "ConnectionStrings": {
    "CosmosDBConnection": "AccountEndpoint=foobar;"
  }
}

in Azure portal > function apps > platform features > configurations > Application settings > New application settings > Name: CosmosDBConnection Value: AccountEndpoint=foobar; update > save

sample c# Azure function 

public static async Task<IActionResult> Run(
           [HttpTrigger(AuthorizationLevel.Anonymous, "get")] HttpRequest req,
           [CosmosDB(databaseName:"dbName",
                     collectionName:"collectionName",
                     ConnectionStringSetting = "CosmosDBConnection")] DocumentClient documentClient, 
           ILogger log){
             .....
           }

Upvotes: 0

Jeson Martajaya
Jeson Martajaya

Reputation: 7352

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

local.settings.json picture

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

Application Settings

Add MyConnectionString

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

enter image description here

Thats it.

Upvotes: 31

Johannes
Johannes

Reputation: 541

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

Upvotes: 28

Emil
Emil

Reputation: 6891

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

  1. 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());
            }
        }
    }
  1. 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:
}

Upvotes: 18

Todd DeLand
Todd DeLand

Reputation: 3175

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;

Upvotes: 84

David Ebbo
David Ebbo

Reputation: 43183

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.

Upvotes: 38

William Yeung
William Yeung

Reputation: 10556

I believe common practice is use environment variables for azure functions, then you can setup the environment variables in the Azure Function:

(Function App Settings -> Configure app settings -> App settings section)

Maybe would be more helpful if you can also let us know which language you are using?

Upvotes: 4

Related Questions