Mssql-docker: Unable to connect to MSSQL from ASPNET Core container with Docker compose

[Myrmex]

I'm trying to use docker compose to connect my ASPNET Core web app to a MSSQL container. This should be something relatively easy, but I always end with 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)..

Here is my compose file (the app also uses a MongoDB):

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

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? Do I need something else in my compose file?

[Myrmex]

For the sake of completeness, I add the full log for the described issue. If no apparent error is found in the compose file, perhaps it could be a timeout issue? Any suggestion is welcome... Thanks

cadmusapi_1    | 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: 40 - Could not open a connection to SQL Server)
cadmusapi_1    |    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
cadmusapi_1    |    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
cadmusapi_1    |    at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
cadmusapi_1    |    at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
cadmusapi_1    |    at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
cadmusapi_1    |    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
cadmusapi_1    |    at System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
cadmusapi_1    | --- End of stack trace from previous location where exception was thrown ---
cadmusapi_1    |    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
cadmusapi_1    |    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
cadmusapi_1    |    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.<OpenAsync>d__34.MoveNext()
cadmusapi_1    | --- End of stack trace from previous location where exception was thrown ---
cadmusapi_1    |    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
cadmusapi_1    |    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
cadmusapi_1    |    at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
cadmusapi_1    |    at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass20_0.<<ExistsAsync>b__0>d.MoveNext()
cadmusapi_1    | --- End of stack trace from previous location where exception was thrown ---
cadmusapi_1    |    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
cadmusapi_1    |    at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass20_0.<<ExistsAsync>b__0>d.MoveNext()
cadmusapi_1    | --- End of stack trace from previous location where exception was thrown ---
cadmusapi_1    |    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
cadmusapi_1    |    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
cadmusapi_1    |    at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
cadmusapi_1    | --- End of stack trace from previous location where exception was thrown ---
cadmusapi_1    |    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
cadmusapi_1    |    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
cadmusapi_1    |    at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
cadmusapi_1    |    at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.<EnsureCreatedAsync>d__18.MoveNext()
cadmusapi_1    | --- End of stack trace from previous location where exception was thrown ---
cadmusapi_1    |    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
cadmusapi_1    |    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
cadmusapi_1    |    at CadmusApi.Services.DatabaseInitializer.<Seed>d__4.MoveNext() in /src/CadmusApi/Services/DatabaseInitializer.cs:line 37
cadmusapi_1    | --- End of stack trace from previous location where exception was thrown ---
cadmusapi_1    |    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
cadmusapi_1    |    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
cadmusapi_1    |    at CadmusApi.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env, IDatabaseInitializer databaseInitializer) in /src/CadmusApi/Startup.cs:line 204
cadmusapi_1    | --- End of stack trace from previous location where exception was thrown ---
cadmusapi_1    |    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
cadmusapi_1    |    at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app)
cadmusapi_1    |    at Microsoft.AspNetCore.Hosting.Internal.AutoRequestServicesStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder builder)
cadmusapi_1    |    at Microsoft.AspNetCore.Hosting.Internal.WebHost.BuildApplication()
cadmusapi_1    | ClientConnectionId:00000000-0000-0000-0000-000000000000
cadmus_cadmusapi_1 exited with code 1

[tompie87]

Try as server in the connectionstring cadmusmssql, docker should handle that

[paulluscastro]

@tompie87 answer worked fine for me. Thank you!