Connect to a database in a Docker container from a VirtualBox virtual machine

Docker is an emerging technology to launch applications on any guest operating system using any host operating system. The same host can run multiple Docker containers of any type, so one physical or virtual server can serve as a complete mini cloud environment. Most of the time Linux (including macOS) is used as the host operating system.

In this example, we will launch a PostgreSQL Docker container on macOS, and access the PostgreSQL database server from a Windows VirtualBox virtual machine.

Thanks to Robert Lestak for the Docker scripts.

Create a network between the guest VM and the host in Oracle VirtualBox

A VirtualBox virtual machine can share the networking with the host with the NAT setting, or it can be an independent computer on the network with the Bridged setting.

To access the host directly from the guest VM we can set up a host-only network, that only the host and the guest can access. This way we can launch a container on the host and access it from the guest via the host-only mini-network.

To set up the private host-only network

  1. Open the VirtualBox application and shut down all virtual machines
  2. In the File menu select Host Network Manager
  3. On the Host Network Manager window click the Create button
  4. Click the Enable checkbox and the Close button

Connect the virtual machine to the host-only network

The VirtualBox virtual machines can have four virtual network adapters. Always use the first adapter to connect to the host-only network.

  1. Select the virtual machine in the list and click the Settings icon
  2. On the Network tab click the arrow next to the Attached to drop-down
  3. Select the Host-only adapter
  4. The host-only network is automatically selected
  5. Select the second adapter, click the Enable Network Adapter checkbox, and select NAT
  6. Click the OK button to close the window.

Find the IP address of the host machine

  1. Open a terminal on the host machine.
  2. On Mac
    1. enter the following command. Use the IP address range of the host-only network above.
      ifconfig | grep 192.168.56

      inet 192.168.56.1 netmask 0xffffff00 broadcast 192.168.56.255

    2. Based on the output  above, the IP address of the host machine is 192.168.56.1

Launch a PostgreSQL Docker container

  1. Create a docker-compose.yml file. Containers are ephemeral, when we terminate them, all data stored inside the container is lost. We want to keep the database files on our local hard drive, so this configuration will map the /var/lib/postgresql/data directory of the Docker container to the pgdata subdirectory of the current working directory. The 5432 is the default port of the PostgreSQL database server, we will expose it on port 5433 of the host.
    version: '3'
    
    services:
      postgres:
        image: postgres:10
        env_file: .env
        ports:
          - "5433:5432"
        restart: always
        volumes:
          - $PWD/pgdata:/var/lib/postgresql/data
  2. Open a terminal window in the same directory and launch the Docker container
    docker-compose up -d

Connect to the host machine from the guest machine

Test the connection

  1. Open a terminal on the guest machine
  2. Test the connection to the host machine
    ping 192.168.56.1

Configure an ASP.NET Core 2.1 application to connect to the Docker PostgreSQL database server

  1. Change the connection string, and specify the exposed port on the host machine:
    "Server=192.168.56.1;Port=5433;Database=MY_DATABASE;Username=MY_USERNAME;Password=MY_PASSWORD"

 

 

Add identity into an ASP.NET Core 2.1 MVC project and maintain full control of the Identity UI

To maintain full control over the Identity UI in an ASP.NET Core 2.1 MVC project, scaffold the identity using the Windows version of Visual Studio 2017.

Scaffold the Identity

  1. Right click the web application project and select Add, New Scaffolded Item
  2. On the left side of the Add Scaffold dialog select Identity, and click the Add button
  3. Select your existing Layout page, so the scaffolding will not overwrite it
  4. Select the Identity functions (files) you want to override, and click the down arrow to select your existing data context class
  5. The MY_SOLUTION\MY_WEBAPP\ScaffoldingReadme.txt file contains tasks to do to set up the Identity UI. Based on that
    1. Add the following lines to the Configure method of the Startup.cs file in this order. If a line is already in the file, make sure the order is correct.
      app.UseStaticFiles()
      app.UseAuthentication()
      app.UseMvc()
    2. Add to the ConfigureServices method of the Startup.cs file, if the line is not there.
      services.AddMvc()
  6. The Aeras, Identity, Pages, Account folder contains the .cshtml files you can modify.
  7. The code is in the .cshtml.cs file

Use your own SMTP server

To configure Identity to use you own SMTP server to send the Forgot my password and email verification emails, register a custom email sender.

  1. Add to the ConfigureServices method of the Startup.cs file
    // using Microsoft.AspNetCore.Identity.UI.Services;
    services.AddSingleton<IEmailSender, EmailSender>();
  2. Create the EmailSender class in the web application project
    // using Microsoft.AspNetCore.Identity.UI.Services;
    // using System.Threading.Tasks;
    public class EmailSender : IEmailSender
    {
        public Task SendEmailAsync(string sTo, string sSubject, string sMessage)
        {
            MyMessagingClass messaging = new MyMessagingClass();
    
            // Send an email message with SMTP
            messaging.MySendEmailMethod(sTo, sSubject, sMessage);
            
            return Task.CompletedTask;
        }
    }

For more information see https://docs.microsoft.com/en-us/aspnet/core/security/authentication/scaffold-identity?view=aspnetcore-2.1&tabs=visual-studio#scaffold-identity-into-a-razor-project-with-authorization

 

 

Add jQuery DataTables grid to an ASP.NET Core MVC web application

ASP.NET does not provide sortable, searchable tables. jQuery DataTables is a popular way to display user-friendly data in an ASP.NET Core MVC web application.

Set up the environment

  1. Add the System.Linq.Dynamic.Core NuGet package to the web application
  2. Add the JsonOptions to the Startup.cs file
     using Newtonsoft.Json.Serialization;
  3. Add the AddJsonOptions to the services.AddMvc() section in the ConfigureServices() method of the Startup.cs file
     .AddJsonOptions(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());

Add the jQuery DataTables grid to the view

  1. Add the script and css references to the bottom of the view
    @section Scripts {
    	<!-- For the DataTables grid -->
    	<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css">
    	<script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
    
    	<!-- Page-specific script -->
    	<script src="~/js/MY-CONTROLLER_MY-PAGE.js" asp-append-version="true"></script>
    }
  2. Add to the page-specific MY-CONTROLLER_MY-PAGE.js file to the wwwroot\js directory
    $(document).ready(function () {
    
      // Set up the DataTable grid
      $('#serverTable').DataTable();
    
    });
  3. Create a table with the same id referenced in the script
    @if (null != Model.ServerList) {
      <table id="serverTable" class="table table-striped table-bordered dt-responsive nowrap" width="100%">
        <thead>
          <tr>
            <th></th>
            <th>Name</th>
            ...
          </tr>
        </thead>
        <tbody>
          @foreach (ServerInstance instance in Model.ServerList) {
            <tr>
              <td><input type="checkbox" asp-for="@instance.Selected" /></td>
              <td>@instance.NameTag</td>
              ...
            </tr>
          }
        </tbody>
      </table>
    }

 

The user account does not have permission to run this task

When a Scheduled task is created by another user (or SYSTEM) most of the time only that user can manually trigger the task execution.

When you try to execute the scheduled task from the user interface you can get the error message:

The user account does not have permission to run this task

This error is caused by the lack of permissions on the XML file that contains the task details. To allow all administrators to execute the scheduled task

  1. Remote into the Windows server
  2. Open the C:\Windows\System32\Tasks folder in Windows Explorer
  3. Right-click the file with the name of the scheduled task and select properties
  4. On the Security tab select Administrators and click the Edit button
  5. Check the Read & execute checkbox and click the OK button
  6. Click Yes on the Windows Security popup

Now you should be able to manually execute the scheduled task

Error unprotecting the session cookie in an ASP.NET Core MVC application.

The new ASP.NET Core MVC framework automatically displays a message for the user to accept the application’s privacy policy. The default message is “Use this space to summarize your privacy and cookie use policy.” No cookies are saved in the user’s browser until they click the Accept button.

Even after accepting the terms, if the browser contains old, stale cookies the application may not be able to save cookies. The log contains

warn: Microsoft.AspNetCore.Session.SessionMiddleware[7]
Error unprotecting the session cookie.
System.Security.Cryptography.CryptographicException: The key … was not found in the key ring.

If the web application works in a new incognito window, clear the old cookies in your browser.

In Google Chrome

  1. In the Chrome menu select Clear Browsing Data
  2. On the Clear browsing data page select All time, and Cookies and other site data

 

 

Set the environment for an ASP.NET Core MVC web application

When the ASP.NET Core MVC web application starts, reads the name of the environment for an environment variable of the host computer. The compiled application contains the configuration for all supported environments, making possible to build and deploy the same artifact in every environment.

If the ASPNETCORE_ENVIRONMENT environment variable is not defined, the Production environment settings are loaded.

In Visual Studio the web application project sets the value on the Debug tab.

To set the environment on the server

Windows

To set the value globally that is preserved after restart

Command prompt

  1. Open the command prompt window and execute
    setx ASPNETCORE_ENVIRONMENT Development /M

PowerShell

  1. Open a PowerShell window and execute
    [Environment]::SetEnvironmentVariable("ASPNETCORE_ENVIRONMENT", "Development", "Machine")

macOS

  1. Add to the .bashrc or .bash_profile file
    export ASPNETCORE_ENVIRONMENT=Development

 

Configure the ASP.NET Core MVC web application

Create the environment-specific appsettings.json files

  1. In the web application project click the arrow next to the appsettings.json file to reveal the Development configuration file
  2. Right-click the appsettings.Development.json file and select Copy

  3. Right click the web application project and select Paste

  4. Rename the appsettings – Copy.Development.json file to appsettings.Production.json

  5. The file will automatically move under the appsettings.json file

Edit the configuration files

  1. Open the appsettings.Production.json file and add the connection string
    {
        "ConnectionStrings": {
            "DefaultConnection": "Server=MY_DATABASE_URL;Database=MY_DATABASE_NAME;Username=MY_USERNAME;Password=MY_PASSWORD"
        }
    }
    
    

 

Return values to the controller in the model from the ASP.NET MVC view

When we create an ASP.NET MVC web application, the model (an object) is the easiest way to return values from the view to the controller.

For the view to be able to return the values in the model, make sure the model contains properties, not fields, for every value with { get; set; }

public class MyViewModel
{
    public string ID { get; set; }
}

Add the model to the view at the top of the .cshtml file

IMPORTANT!!! To return values for properties that do not have controls on the page, like IDs that are not displayed, add a hidden field to the view.

@model MyViewModel
<form  asp-action="Index" asp-controller="My">
    <input asp-for=@Model.ID type="hidden" >
</form>

Send the data in the model to the view from the get method in the controller

public class MyController : Controller
{
    public IActionResult Index()
    {
         MyViewModel model = new MyViewModel();
         model.ID = "MY_ID";
         return View(model);
    }
}

Read the value from the model in the post method of the controller and send the model back

public class MyController : Controller
{
    [HttpPost, ValidateAntiForgeryToken]
    public IActionResult Index(MyViewModel model)
    {
        string ID = model.ID;
...
        return View(model);
    }
}

 

Error loading table definition of a PostgreSQL table

When a PostgreSQL table name contains uppercase letters there is no known way to get the table definition SQL script.

When we right-click a table name in SQLPro for Postgres the has an uppercase letter and select Script as, CREATE to, New query window

we get the error message

/*
* Error loading table definition.
* ERROR: relation “public.users” does not exist
*/

As the error message tells us, PostgreSQL is looking for the table name with all lowercase letters.


The pg_dump utility acts the same way.

pg_dump -s -h MY_SERVER_ADDRESS -U M_USERNAME -t Users MY_DATABASE

The error message is

pg_dump: no matching tables were found


The same process works if the table name only contains lowercase letters.

Convert an existing PostgreSQL database to all lowercase table and column names

To avoid similar errors caused by upper case letters in table and column names, convert the existing database definition to all lowercase

    1. Backup your database from the command prompt
      PGPASSWORD=MY_PASSWORD pg_dump -h MY_DATABASE_ADDRESS -U MY_DATABSE_USER -f MY_BACKUP_FILENAME.bak MY_DATABASE_NAME
    2. Open the backup file in a text editor
    3. Add underscores to the table and column names to match the .NET entity framework user identity processes.
      1. The PostgreSQL driver converts the first letter of all lowercase table and column names to upper-case and the letter that follows an underscore. For example, asp_net_users will be converted to AspNetUsers.
        1. Replace the table names with TableName -> table_name ( leave __EFMigrationsHistory as is)
        2. Replace the column names with “ColumnName” -> “column_name”
        3. Replace the primary key names with PK_ -> pk_
        4. Replace the index names with IX_ -> ix_
        5. Replace the foreign key names with FK_ -> fk_
    4. Drop the database. In SQL Pro for Postgres
      1. Right-click the database and select Drop database
    5. Re-create the database. In SQL Pro for Postgres
      1. Rith-click the server name and select Create database
    6. Import the modified backup file from the command prompt
      PGPASSWORD=MY_PASSWORD psql -h MY_DATABASE_ADDRESS -U MY_DATABSE_USER -d MY_DATABASE_NAME -f MY_BACKUP_FILENAME.bak
    7. Reverse engineer the database back to the .NET data layer project
      1. Delete the class files that correspond to the database tables
      2. Delete the MY_DATABSEContext.cs file
      3. Import execute the command in the data layer project directory
        dotnet ef dbcontext scaffold --force "Host=MY_DATABASE_ADDRESS;Database=MY_DATABASE_NAME;Username=MY_DATABSE_USER;Password=MY_PASSWORD" Npgsql.EntityFrameworkCore.PostgreSQL

Reverse engineer a database with AspNetCore in Visual Studio

For some reason the .NETCore designers did not think, that developers want to follow best practices by separating the data layer from the presentation layer.

The Entity framework out of the box only works if the database is accessed from the main application project.

When we try to reverse engineer a PostgreSQL database from a class library with the command:

cd MY_CLASS_LIBRARY_DIRECTORY
dotnet ef dbcontext scaffold "Host=localhost;Database=MY_DATABASE_NAME;Username=MY_USERNAME;Password=MY_PASSWORD" Npgsql.EntityFrameworkCore.PostgreSQL

we get the error message:

The specified framework version ‘2.1’ could not be parsed
The specified framework ‘Microsoft.NETCore.App’, version ‘2.1’ was not found.
– Check application dependencies and target a framework version installed at:
/usr/local/share/dotnet/
– Installing .NET Core prerequisites might help resolve this problem:
http://go.microsoft.com/fwlink/?LinkID=798306&clcid=0x409
– The .NET Core framework and SDK can be installed from:
https://aka.ms/dotnet-download
– The following versions are installed:
2.0.0 at [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
2.1.2 at [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
2.1.3 at [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]

The problem is, that the …runtimeconfig.json files are only automatically generated in the bin/netcoreapp2.1 directory of the main application project, those are missing from all class libraries.

To enable the automatic generation of the …runtimeconfig.json files, add a line to the <PropertyGroup> section of the data layer class library project (.csproj) file.

<PropertyGroup>
...
<GenerateRuntimeConfigurationFiles>True</GenerateRuntimeConfigurationFiles>
...
</PropertyGroup>