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?

Edward · Accepted Answer · 2019-08-01 08:52:48Z

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

Follow steps below:

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"

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:

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

Big X · Accepted Answer · 2019-07-31 07:35:20Z

0

As this is not clear, I am assuming that both your service and the db are dockerized.

Then first, in the appsettings.json you have to specify the IP-address of the docker host instead of localhost, since localhost only refers to the localhost of the container itself. You might want to read more about docker networking here.

Second, I see no reason to overcomplicate the docker-compose.yml by also specifying an override.yml, but it has to be docker-compose.override.yml instead of docker-compose-override.yml. For debugging it might be helpful to see how a container is really started (especially if using docker-compose) with the help of docker inspect.

answered Jul 31, 2019 at 7:35

Big X

981 silver badge9 bronze badges

Collectives™ on Stack Overflow

SQL Server connection with Docker and Entity Framework

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related