CODEWITHSUNDEEP
sql-serverentity-frameworkdockerasp.net-coredatabase-connection
Dr. Strangelove
Dr. Strangelove

Reputation: 3328

SQL Server connection with Docker and Entity Framework

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?

Upvotes: 7

Views: 6770

Answers (2)

Edward
Edward

Reputation: 29986

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;

Upvotes: 10

Big X
Big X

Reputation: 98

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.

Upvotes: 0

Related Questions