Azure Web App error 500 on Connection String

0
votes

I've a simple ASP.NET Core 1.0 application which seeds some data in to SQL Server in Azure. I've 2 databases, one is for development and other is for Production (Azure SQL), I'm using Identity. When I run my application on my local machine, it works fine, but when I deploy it I get the error 500 (Internal Server Error) with no further explanation here is my code.

if (CurrentEnvironment.IsDevelopment())
        {
            services.AddDbContext<ApplicationDbContext>(options => 
                                                        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
        }
        else if (CurrentEnvironment.IsStaging() || CurrentEnvironment.IsProduction())
        {
            services.AddDbContext<ApplicationDbContext>(options =>
                                                        options.UseSqlServer(Environment.GetEnvironmentVariable("SQLCONNSTR_kConnectionString")));
        }

If I change the environment setting from Production to Development using Azure Application Settings, it says that error is at Configuration.GetConnectionString("DefaultConnection") saying connection string cannot be null.

3
azureasp.net-coreasp.net-core-1.0
Do you store your connection string in a json (user secrets or appsettings.json) or in environmental variables? And what's the structure? It should look like "ConnectionStrings": { "DefaultConnection": "..." }. Note that this is a change to previous versions of ASP.NET Core (i.e. rc1), where it was "Data" : { "DefaultConnection" : { "ConnectionString" : "..." }} before - Tseng

3 Answers

1
votes

There is a special pattern to be used, when you want to set the Variables from Environmental Variables.

In Azure App Service you set up your connection string as "DefaultConnection" or "SQLCONNSTR_kConnectionString" respectively, but you need to use. So if your connection string is "DefaultConnection", your appsetting.json must look like this 

"ConnectionStrings" : {
    "DefaultConnection" : "..."
}

when you obtain it with Configuration.GetConnectionString("DefaultConnection") (alternatively you can get it via Configuration["ConnectionStrings:DefaultConnection"]).

If you load it from environmental variables, it's better to override the default connection string via 

 var builder = new ConfigurationBuilder()
            .SetBasePath(hostEnv.ContentRootPath)
            .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
            .AddJsonFile($"appsettings.{hostEnv.EnvironmentName}.json", optional: true, reloadOnChange: true);
            .AddEnvironmentVariables();

Then the environmental variables will automatically override your settings in the appsettings.json or appsettings.Production.json.

If you want to get it via Environment.GetEnvironmentVariable you need to make it Environment.GetEnvironmentVariable("ConnectionStrings__SQLCONNSTR_kConnectionString") on Linux and Environment.GetEnvironmentVariable("ConnectionStrings:SQLCONNSTR_kConnectionString") on Windows. It's on top of my head, but the convention was something similar to this

1
votes

I might misunderstand what you meant by "Azure SQL", but if you're using hosted Azure DB, then the prefix for the environment variable will be SQLAZURECONNSTR_ instead of SQLCONNSTR_. The latter would be used, for example, if you have SQL on your own VM in Azure (IaaS).

Also, it may help you to break apart your combined line of:

services.AddDbContext<ApplicationDbContext>(options =>
                                                        options.UseSqlServer(Environment.GetEnvironmentVariable("SQLCONNSTR_kConnectionString")));

into separate lines, something like the following, then you might be able to diagnose the issue a bit easier.

var envConnectionString = Environment.GetEnvironmentVariable(...);

if (string.IsNullOrEmpty(envConnectionString))
{
  // log error, throw exception, etc      
}
else
{
  services.AddDbContext<ApplicationDbContext>(options => 
                                              options.UseSqlServer(envConnectionString)));
}
1
votes

Thank for your respond, actually I resolved my issue. Since ASP.NET Core RC 2, we have to use 

"publishOptions": {
"include": [
  "wwwroot",
  "Views",
  "Areas/**/Views",
  "appsettings.json",
  "web.config"
] },

options in project.json in order work them. :)