Docker api not connecting to SQL Server in Docker

Question

I am trying to run my application in docker while also creating a SQL Server.

It does not seem to connect to my SQL Server and I get the following error:

Unhandled exception. Microsoft.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: Could not open a connection to SQL Server)

This is how my docker-compose file looks like.

version: '3.7'

services:
  musicapi:
    build:
      context: .
    ports:
      - 80:80
    environment:
      Authentication:Enabled: 'true'
    restart: always
    depends_on:
      - testdb

  testdb:
    image: mcr.microsoft.com/mssql/server:2017-latest
    ports: 
      - "1433:1433"
    environment:
      TZ: Europe/Amsterdam
      ACCEPT_EULA: "y"
      SA_PASSWORD: Password01
    volumes:
      - mssql_data:/var/opt/mssql/data       
    restart: always

volumes:
  mssql_data:

And my Dockerfile:

FROM mcr.microsoft.com/dotnet/aspnet:6.0-alpine AS base
WORKDIR /app
EXPOSE 80

FROM mcr.microsoft.com/dotnet/sdk:6.0 as build
WORKDIR /src

COPY . .

RUN dotnet restore
RUN dotnet build -c Release
RUN dotnet publish -c Release -o ./out 

FROM base AS final
WORKDIR /app
COPY --from=build /src/out .

# The following 2 lines are required for solving: 
# "Globalization Invariant Mode is not supported" error.
RUN apk add --no-cache icu-libs
ENV DOTNET_SYSTEM_GLOBALIZATION_INVARIANT=false

ENTRYPOINT ["dotnet", "Test.Api.dll"]

My connection string looks like this:

"ConnectionStrings": 
{
    "DefaultConnection": 
         "Server=localhost,1433;Database=test.db;User ID=sa;Password=Password01;TrustServerCertificate=True;"
}

What am I doing wrong?

Answer 1

In my case, this is my dockercompose: The BD Host is the name of the service of the sql server container:

version: '3.4'

networks:
  vnetbackend:  

services:
  backendcrud.minimal.api:
    container_name: crudminimalapi2024
    image: ${DOCKER_REGISTRY-}backendcrudminimalapi
    build:
      context: .
      dockerfile: BackendCRUD.Minimal.Api/Dockerfile
    environment:
      - DB_SERVER_HOST=servicio_contenedorbd
      - DB_SERVER_PORT=1433
      - DB_NAME=BD_Team
      - DB_USER=sa
      - DB_SA_PASSWORD=Password1*      
    networks:
      - vnetbackend
    ports:
      - "12000:8082"
    depends_on:
      - servicio_contenedorbd 
  servicio_contenedorbd:
    container_name: dbcontainer
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=Password1*
    networks:
      - vnetbackend
    ports:
        - "8001:1433"

Here my file:

If you need read in C# using EF Core,in OnConfiguring you can read environment values using Environment.GetEnvironmentVariable sentence:

And then , en Docker Desktop you can see:

Answer 2

Each container is a separate network entity, so when your connection string specifies Server=localhost, that means that the database is in the same container as your application.

On the Docker network that docker-compose creates, the containers can talk to each other using the service names as the host names. In your case, the database container can be reached using the host name testdb.

So you connection string should be

Server=testdb,1433;Database=test.db;User ID=sa;Password=Password01;TrustServerCertificate=True;