The first thing we need to do is to change the default SQL Server authentication mode, so we won't be forced to use an existing Windows account. To do so, right-click on the root tree view node, which represents our SQL Server instance, and select Properties from the contextual menu. From the modal window that appears, select the Security page, then switch from Windows Authentication mode to SQL Server and Windows Authentication mode:

Now we can create the database that will host our application's tables. Right-click on the Databases folder and choose Add Database from the contextual menu. Give it the OpenGameList name and click on OK.

Go back to the root Databases folders, then expand the Security folder, which should be just below it. From there, right-click on the Logins subfolder and choose New Login. Again, give it the OpenGameList name. From the radio button list below, select SQL Server Authentication and set a suitable password (for example, SamplePassword123), then click on OK.

The next thing we need to do is to properly map this login to the OpenGameList database we added earlier. From the navigation menu to the left, switch to the User Mapping tab. Click on the checkbox right to the OpenGameList database, then write OpenGameList in the User cell and assign the db_owner membership role:

As soon as we click on the OK button, a new OpenGameList user will be added to the OpenGameList database with full administrative rights.

We can easily confirm that by going back to the root Databases folder and expanding it to OpenGameList | Security | Users:

That's it! Now we'll be able to access our brand new OpenGameList database with a standard connection string using the credentials we just created.

Now that the SQL Server database has been set up, we need to tell our application to use it instead of localDb while in production. We can easily do that by adopting the ASP.NET Core default pattern for configuring application behavior across multiple environments.

To implement it within our project, we need to perform the following steps:

Right-click to the project's root and select Add | New Item. Choose ASP.NET Configuration File, name it appsettings.production.json, and click on OK.

An environment-specific version of the appsettings.json file will be created accordingly.

Visual Studio should also nest it below the main configuration file, as can be seen in the following screenshot:

From now on, any key/value pair we'll include within the appsettings.production.json file will override the corresponding key/value in the main configuration file whenever the application will run within a production environment. It means that we can use it to redefine a number of production-specific values there, including (yet not limited to) the default connection string.

As a matter of fact, that's precisely what we need to do. Open the new appsettings.production.json file and replace the sample contents with the following code:

{ 
  "Data": { 
    "DefaultConnection": { 
      "ConnectionString": "Server=localhost\SQLEXPRESS;Database=OpenGameList;User Id=OpenGameList;Password=SamplePassword123;Integrated Security=False;MultipleActiveResultSets=True" 
    } 
  } 
} 

"Authentication": { 
  "OpenIddict": { 
    "Authority": "http://www.your-website-url.com/" 
  } 
}, 

"Logging": { 
  "IncludeScopes": false, 
  "LogLevel": { 
    "Default": "Warning", 
    "System": "Error", 
    "Microsoft": "Error" 
  } 
}, 

"StaticFiles": { 
  "Headers": { 
    "Cache-Control": "max-age=3600", 
    "Pragma": "cache", 
    "Expires": null 
  } 
} 

If we implemented one or more external providers (see Chapter 8, Third-Party Authentication and External Providers), we'll probably need to replace the redirect URIs we configured earlier and also add our public facing URL (www.our-website-url.com) to the allowed JavaScript origins URL list. For a detailed guide about how to do that, we can refer to Chapter 8, Third-Party Authentication and External Providers.

We now need to update the root project.json file to include these files whenever we publish our application. Within that file, look for the publishOptions key and change the include array values to the following (updated lines are highlighted):

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

This small change ensures that, whenever we publish our application, each and every appsettings file will be included as well.

The application settings file(s) loading logic can be customized within the Startup method of the Startup class.

The Startup.cs file bundled with the default ASP.NET Core project template that we chose back in Chapter 1, Getting Ready, already features a cascading logic in place that perfectly suits our needs. All we need to do is to ensure that it's still there. We can easily check that by opening the Startup.cs file and having a look at the two AddJsonFile method calls within the constructor:

public Startup(IHostingEnvironment env) 
{ 
    var builder = new ConfigurationBuilder() 
        .SetBasePath(env.ContentRootPath) 
        .AddJsonFile( 
"appsettings.json", 
            optional: true, 
            reloadOnChange: true) 
        .AddJsonFile( 
$"appsettings.{env.EnvironmentName}.json", 
            optional: true) 
        .AddEnvironmentVariables(); 
    Configuration = builder.Build(); 
} 

We can see how the first highlighted line will load the default appsettings.json file, while the second will look for an (optional) environment-specific version containing the relevant value overrides. If everything is still in place, we don't need to change anything, since the existing behavior perfectly fits what we want.

Last but not least, we need to set up our app so that it will run in a production environment whenever we publish it.

To do that, open the /properties/launchSettings.json file and change the ASPNETCORE_ENVIRONMENT variable within our application's profile from Development to Production in the following way:

"OpenGameListWebApp": { 
  "commandName": "Project", 
  "launchBrowser": true, 
  "launchUrl": "http://localhost:5000/api/values", 
  "environmentVariables": { 
    "ASPNETCORE_ENVIRONMENT": "Production" 
  } 
} 

This will ensure the proper loading of the appsettings.production.json file.