Setting the SQL connection string for ASP.NET Core web app in Azure

Question

I have created a new ASP.NET Core web application in Visual Studio 2015. I've also set up an Azure web app to pull in the app from GitHub and run it. This works fine, but I'm having trouble connecting to the database on Azure.

Locally, this works, and it uses config.json and in code Data:DefaultConnection:ConnectionString for the connection string.

How can I leave the code as it is, and have it work in Azure too? I've tried setting the Application Settings in the portal, both the Connection Strings and the App Settings. And using both "SQLCONNSTR_DefaultConnection" and "Data:DefaultConnection:ConnectionString" as the key.

(Setting the App Settings doesn't seem to work by the way. I think the value I provide is too long).

So how can I provide the connection string for my Azure database to my Azure web app (ASP.NET 5), without checking it in in source control?

Update My Startup.cs looks like this (see the full file on GitHub):

public Startup(IHostingEnvironment env)
{
    var configuration = new Configuration()
        .AddJsonFile("config.json")
        .AddJsonFile($"config.{env.EnvironmentName}.json", optional: true);

    if (env.IsEnvironment("Development"))
    {
        configuration.AddUserSecrets();
    } 

    configuration.AddEnvironmentVariables();
    Configuration = configuration;
}

In the ConfigureServices method, there is also:

services.Configure<AppSettings>(Configuration.GetSubKey("AppSettings"));

and, also in the ConfigureServices method:

services.AddEntityFramework()
            .AddSqlServer()
            .AddDbContext<ApplicationDbContext>(options => 
                   options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]))
            .AddDbContext<InvoicesDbContext>(options => 
                   options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));
// So this is where I want my app in Azure to use the connection string I
// provide in the portal

Answer 1

Short answer

I've tried setting the Application Settings in the portal, both the Connection Strings and the App Settings. And using both "SQLCONNSTR_DefaultConnection" and "Data:DefaultConnection:ConnectionString" as the key.

You're close.

1. Go to Azure web app > configure > connection strings.
2. Add a connection string with the name DefaultConnection.
3. Use Configuration.Get("Data:DefaultConnection:ConnectionString") to access it.

Example using timesheet_db instead of DefaultConnection

This is an example from my own timesheet application. My connection string was named timesheet_db. Just replace all instances of that string with DefaultConnection to adapt the example to your use case.

Azure web app configuration

Azure web app service control manager

The online service control manager at https://myWebAppName.scm.azurewebsites.net/Env will show the connection strings.

Startup.cs

Setup configuration settings in Startup so that the environmental variables overwrite the config.json.

public IConfiguration Configuration { get; set; }
public Startup()
{
    Configuration = new Configuration()
        .AddJsonFile("config.json")
        .AddEnvironmentVariables();    <----- will cascade over config.json
}

Configure the database in Startup.

public void ConfigureServices(IServiceCollection services)
{
    services
        .AddEntityFramework()
        .AddSqlServer()
        .AddDbContext<ProjectContext>(options =>
        {
            var connString =
                Configuration.Get("Data:timesheet_db:ConnectionString");
            options.UseSqlServer(connString);
        });
}

Of course, the example uses a connection string named timesheet_db. For you, replace all instances of it with your own connection string named DefaultConnection and everything will work.

Answer 2

In RC2 I had to change how my connection strings were read to get them to work in Azure. In my case I had to ensure the Azure connection string was named "DefaultConnection", and accessed by:

RC1:

{
    "Data": {
        "DefaultConnection": {
            "ConnectionString": "Server=(localdb)\\MSSQLLocalDB;Database=db;Trusted_Connection=True;"
        }
    }
}

Accessed by:

var conn = Configuration["Data:DefaultConnection:ConnectionString"];

RC2:

{
  "Data": {

  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB;Database=db;Trusted_Connection=True;"
  }
}

Accessed by:

var conn = Configuration.GetConnectionString("DefaultConnection");

Answer 3

You have a number of options to set your connection string. The default setup class get the enviroment settings from different sources. You could set your connection string in config.production.json. or config.staging.json. See the startup class

    public Startup(IHostingEnvironment env)
    {
        // Setup configuration sources.
        var configuration = new Configuration()
            .AddJsonFile("config.json")
            .AddJsonFile($"config.{env.EnvironmentName}.json", optional: true);

        if (env.IsEnvironment("Development"))
        {
            // This reads the configuration keys from the secret store.
            // For more details on using the user secret store see http://go.microsoft.com/fwlink/?LinkID=532709
            configuration.AddUserSecrets();
        }
        configuration.AddEnvironmentVariables();
        Configuration = configuration;
    }