SQL Server connection with Docker and Entity Framework

Question

I am trying to setup a database connection for a service I am deploying on docker, where both the service and database are dockerized.

When I POST a payload to an API of the service, I get the following error:

SqlException: 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)

The API is defined as:

    [HttpPost]
    public async Task<IActionResult> Postfoo([FromBody] Foo foo)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        _context.Foos.Add(foo);
        await _context.SaveChangesAsync();

        return CreatedAtAction("GetFoo", new { id = foo.ID }, foo);
    }

The database connection is defined as the following in appsettings.json:

"ConnectionStrings": {
    "DefaultConnection": "Server=tcp:127.0.0.1,1433;Trusted_Connection=True;MultipleActiveResultSets=true;User ID=sa;Password=Pass@word"
},

The start-up logic in the Startup.cs, where db connection is defined, is as the following:

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
        services.AddDbContext<WorkflowContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
    }

In docker-compose.yml, the database service is defined as:

version: '3.4'

services:

  sql.data:
    image: microsoft/mssql-server-linux:2017-latest

and in docker-compose.override.yml, the database-related section is defined as:

version: '3.4'

services:

  sql.data:
    environment:
      - SA_PASSWORD=Pass@word
      - ACCEPT_EULA=Y
    ports:
      - "1433:1433"

I may have overlooked, though still cannot see what is missing?

Answer 1

For sharing network between two containers, we could use docker-compose which will create the shared network.

Follow steps below:

1. docker-compose

   version: '3.4'
   
   services:
   coredocker:
       image: coredocker
       build:
       context: .
       dockerfile: CoreDocker/Dockerfile
   sql.data:
       image: microsoft/mssql-server-linux:2017-latest
       environment:
       - SA_PASSWORD=Pass@word
       - ACCEPT_EULA=Y
       ports:
       - "1433:1433"
   

2. appsettings.json

   {
       "ConnectionStrings": {
           "DefaultConnection": "Server=sql.data,1433;Database=CoreDocker;MultipleActiveResultSets=true;User ID=sa;Password=Pass@word"
       },
       "Logging": {
           "LogLevel": {
           "Default": "Warning"
           }
       },
       "AllowedHosts": "*"
   }
   

Here are two points:

1. Change Server=tcp:127.0.0.1,1433; to Server=sql.data,1433 which is the service name and port
2. Remove Trusted_Connection=True;