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;
The best way to do this is to add a Connection String from the Azure portal:
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.
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/
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
local.settings.json, specify your connection strings inside Values node
portal.azure.com > your Azure Function > function node > Application Settings
GetEnvironmentVariable from your Azure Function (cant get stackoverflow to display this code correctly)
Thats it.
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.
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.
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.
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
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());
}
}
}
var connString = ConfigurationManager.ConnectionStrings["myDBEntities"].ConnectionString; using (var dbContext = new myDBEntities(connString)) { //TODO: }
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:
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
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?
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 portaljust replace the connection string onlocal.settings.jsonfile. It will work accordingly. See the screen shot below:
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;
}
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
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://docs.microsoft.com/en-us/azure-stack/user/azure-stack-key-vault-manage-portal?view=azs-2002
https://docs.microsoft.com/en-us/aspnet/core/security/key-vault-configuration?view=aspnetcore-3.1
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");
}
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){
.....
}
