Unable to connect my app to MySql service in Docker composer network

Updated: see below

I'm new to Docker and trying to compose a .NET Core 2.0 web API in a Ubuntu 18 host, using Docker 18.05.0-ce (build f150324) and these services, all in the same network:

1. SQL Server database;
2. MySql database (https://github.com/docker-library/docs/tree/master/mysql/);
3. Mongo database.

My docker compose file provides these services from their respective images, as reported below. In short:

• SQL Server from image microsoft/mssql-server-linux, port 1433, sa user password set via environment variable SA_PASSWORD after accepting the EULA via ACCEPT_EULA;
• MongoDB from image mongo, port 27017;
• MySql from image mysql/mysql-server, port 3306, password for the root user set via the environment variable MYSQL_ROOT_PASSWORD. I essentially used these sources to configure the service: https://github.com/docker-library/docs/tree/master/mysql#mysql_database and https://docs.docker.com/samples/library/mysql/#environment-variables.

Of course, the web API accessing these services uses other credentials in its development environment, but I'm overriding them via environment variables to adjust the system to Docker (in ASPNET Core 2, as you can see from https://github.com/aspnet/MetaPackages/blob/dev/src/Microsoft.AspNetCore/WebHost.cs, the CreateDefaultBuilder method already includes the environment variables as a configuration source):

• DATA__DEFAULTCONNECTION__CONNECTIONSTRING: the connection string to Sql Server, using SA with the same password set for the Docker service (see above): "Server=sqlserver\\sqlexpress,1433;Database=lexmin;User Id=SA;Password=P4ss-W0rd!;MultipleActiveResultSets=true". Note that for Windows we use : as the configuration hierarchy separator, but for non-Windows OSes we must use __ (found it out at https://github.com/aspnet/Configuration/issues/469).
• SERILOG__CONNECTIONSTRING is the same connection used by the Serilog logger.
• LEX__CONNECTIONSTRING is the MongoDB connection string: mongodb://mongo:27017/lex_catalog.
• ZAX__CONNECTIONSTRING is the MySql connection string: Server=mysql;Database=zax_master;Uid=root;Pwd=password;SslMode=none.
• the other environment variables are for using the MySql and Mongo command line tools for dumping a database, as I should invoke it from my API (as you can see from the script, I still have to find out the exact location of these executables in the Ubuntu environment, but this is a detail).

Here is the corresponding appsettings.json in my web API (shortened), which shows the paths corresponding to the environment variables names:

{
    "Data": {
      "DefaultConnection": {
        "ConnectionString": "..."
      }
    },
    "Serilog": {
      "ConnectionString": "...",
      "TableName": "Log",
      "MinimumLevel": {
        "Default": "Debug",
        "Override": {
          "Microsoft": "Information",
          "System": "Warning"
        }
      }
    },
    "Lex": {
      "ConnectionString": "..."
    },
    "Zax": {
      "ConnectionString": "..."
    },
    "Environment": {
      "MongoDirectory": "...",
      "MySqlDirectory": "...",
      "MySqlDumpUser": "root",
      "MySqlDumpPassword": "..."
    }
  }

Now, when I run docker-compose up, all the services start OK, MySql included; yet, my app throws an exception when trying to connect to MySql: Application startup exception: MySql.Data.MySqlClient.MySqlException (0x80004005): Host '172.19.0.5' is not allowed to connect to this MySQL server.

Could anyone help? Here is my composer script:

version: '3.4'


services:
  # SQL Server at default port
  lexminmssql:
    image: microsoft/mssql-server-linux
    container_name: sqlserver
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: "P4ss-W0rd!"
    ports:
      - 1433:1433
    networks:
      - lexminnetwork

# MongoDB - at default port
  lexminmongo:
    image: mongo
    container_name: mongo
    ports:
      - 27017:27017
    networks:
      - lexminnetwork

# MySql at default port
  lexminmysql:
    image: mysql/mysql-server
    container_name: mysql
    environment:
      # the password that will be set for the MySQL root superuser account
      MYSQL_ROOT_PASSWORD: "password"
    ports:
      - 3306:3306
    networks:
      - lexminnetwork

# Web API
  lexminapi:
    image: naftis/lexminapi
    ports:
      - 58942:58942
    depends_on:
      - lexminmssql:
        condition: service_healthy
      - lexminmongo:
        condition: service_healthy
      - lexminmysql:
        condition: service_healthy
    build:
      context: .
      dockerfile: LexminApi/Dockerfile
    environment:
      # for Windows use : as separator, for non Windows use __
      # (see https://github.com/aspnet/Configuration/issues/469)
      DATA__DEFAULTCONNECTION__CONNECTIONSTRING: "Server=sqlserver\sqlexpress,1433;Database=lexmin;User Id=SA;Password=P4ss-W0rd!;MultipleActiveResultSets=true"
      SERILOG__CONNECTIONSTRING: "Server=sqlserver\sqlexpress,1433;Database=lexmin;User Id=SA;Password=P4ss-W0rd!;MultipleActiveResultSets=true"
      LEX__CONNECTIONSTRING: "mongodb://mongo:27017/lex_catalog"
      ZAX__CONNECTIONSTRING: "Server=mysql;Database=zax_master;Uid=root;Pwd=password;SslMode=none"
      # TODO: locate BIN directories in Linux
      ENVIRONMENT__MONGODIRECTORY: ""
      ENVIRONMENT__MYSQLDIRECTORY: ""
      ENVIRONMENT__MYSQLDUMPUSER: "root"
      ENVIRONMENT__MYSQLDUMPPASSWORD: "password"
    networks:
      - lexminnetwork
    volumes:
      - ./zax-users.xml:/etc/lexmin/zax-users.xml

# Web app
  # TODO

networks:
  lexminnetwork:
    driver: bridge

ADDITION

Thank you, I'm trying to work on these connection issues one at a time, as I found out that the same happens for SQL Server in my composed containers. So I'm extending the question to SQL Server too, but I suppose it's better keeping the discussion in the same post as the issues seem similar.

I started with a smaller set of containers, ruling out MySql at present. My composer is:

version: '3.4'

services:
  # SQL Server
  cadmusmssql:
    image: microsoft/mssql-server-linux
    container_name: cadmussqlserver
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: "P4ss-W0rd!"
    ports:
      - 1433:1433
    networks:
      - cadmusnetwork

  # MongoDB
  cadmusmongo:
    image: mongo
    container_name: cadmusmongo
    environment:
      - MONGO_DATA_DIR=/data/db
      - MONGO_LOG_DIR=/dev/null
    ports:
      - 27017:27017
    command: mongod --smallfiles --logpath=/dev/null # --quiet
    networks:
      - cadmusnetwork

  cadmusapi:
    image: ...myprivaterepoimage...
    environment:
      - ASPNETCORE_ENVIRONMENT=Production
    ports:
      - 60304:60304
    depends_on:
      - cadmusmssql
      - cadmusmongo
    environment:
      DATA__DEFAULTCONNECTION__CONNECTIONSTRING: "Server=127.0.0.1\\sqlexpress,1433;Database=cadmusapi;User Id=SA;Password=P4ss-W0rd!;MultipleActiveResultSets=true"
      SERILOG__CONNECTIONSTRING: "Server=127.0.0.1\\sqlexpress,1433;Database=cadmusapi;User Id=SA;Password=P4ss-W0rd!;MultipleActiveResultSets=true"
    networks:
      - cadmusnetwork

networks:
  cadmusnetwork:
    driver: bridge

By googling around, I found that it is required to explicitly set the port number in the connection string. Thus, even if currently I'm overriding environment variables in the docker compose file, I added them in my appsettings.Production.json, too. In my Program.cs Main method, I setup the configuration like this (for Serilog: see http://www.carlrippon.com/?p=1118):

IConfiguration configuration = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
    .AddJsonFile(
        $"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? "Production"}.json",
        optional: true)
    .Build();

So this overrides the appsettings.json file with appsettings.Production.json if ASPNETCORE_ENVIRONMENT is not specified. Anyway, just to make it clearer, in my composer I have added it:

environment:
  + ASPNETCORE_ENVIRONMENT=Production

To check my environment variables, I added code to dump it at my app startup. The dump has the expected connection strings:

cadmusapi_1    | ASPNETCORE_PKG_VERSION = 2.0.8
cadmusapi_1    | ASPNETCORE_URLS = http://+:80
cadmusapi_1    | DATA__DEFAULTCONNECTION__CONNECTIONSTRING = Server=127.0.0.1\sqlexpress,1433;Database=cadmusapi;User Id=SA;Password=P4ss-W0rd!;MultipleActiveResultSets=true
cadmusapi_1    | DOTNET_DOWNLOAD_SHA = d8f6035a591b5500a8b81188d834ed4153c4f44f1618e18857c610d0b332d636970fd8a980af7ae3fbff84b9f1da53aa2f45d8d305827ea88992195cd5643027
cadmusapi_1    | DOTNET_DOWNLOAD_URL = https://dotnetcli.blob.core.windows.net/dotnet/Runtime/2.0.7/dotnet-runtime-2.0.7-linux-x64.tar.gz
cadmusapi_1    | DOTNET_RUNNING_IN_CONTAINER = true
cadmusapi_1    | DOTNET_VERSION = 2.0.7
cadmusapi_1    | HOME = /root
cadmusapi_1    | HOSTNAME = 29884ca26699
cadmusapi_1    | PATH = /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
cadmusapi_1    | SERILOG__CONNECTIONSTRING = Server=127.0.0.1\sqlexpress,1433;Database=cadmusapi;User Id=SA;Password=P4ss-W0rd!;MultipleActiveResultSets=true

Here is the dump code, maybe it might be useful for a quick copy-and-paste:

private static void DumpEnvironment()
{
    IDictionary dct = Environment.GetEnvironmentVariables();
    List<string> keys = new List<string>();
    var enumerator = dct.GetEnumerator();
    while (enumerator.MoveNext())
    {
        keys.Add(((DictionaryEntry)enumerator.Current).Key.ToString());
    }

    foreach (string key in keys.OrderBy(s => s))
        Console.WriteLine($"{key} = {dct[key]}");
}

Yet, I keep getting the connection error from SqlServer like: Application startup exception: System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 35 - An internal exception was caught).

As the services start, I tried to connect to them from my (Ubuntu) Docker host with IP 127.0.0.1. I installed SQL Operations Studio, and connected to 127.0.0.1\sqlexpress,1433 with username SA and the password specified in the compose file, and this works fine. So, how does it happen that the same authentication parameters fail when used from my ASP.NET Core app in its container?

This to me looks like a MySQL security error. Basically you need to configure MySQL to allow external connections.

There's a very good thread here that goes through the trouble shooting steps to get this sorted.