Why can't connect my .net core container to the SQL Server docker container?

Question

It fails to connect to the SQL Server container, even if the backend is running in local and the database in docker.

I tested with Azure SQL and everything worked perfectly.

Error(s):

1. After docker-compose up, and try to add element to the list:

2021-07-13 21:43:32.79 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'AsdDB'. [CLIENT: XXX.168.XX.3]
backend    | fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
backend    |       An error occurred using the connection to database 'AsdDB' on server 'sqlserver'.
backend    | fail: Microsoft.EntityFrameworkCore.Query[10100]
backend    |       An exception occurred while iterating over the results of a query for context type 'Asd.Db.AppDbContext'.
backend    |       Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot open database "AsdDB" requested by the login. The login failed.
backend    |       Login failed for user 'sa'.

2. dotnet ef update database

dotnet ef database update --project Asd.Api.csproj
Build started...
Build succeeded.

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Backend Dockerfile:

FROM mcr.microsoft.com/dotnet/core/sdk:3.1-bionic AS build
WORKDIR /app



EXPOSE 80


COPY *.sln . 
COPY Asd.Api/*.*.csproj ./Asd.Api/
COPY Asd.Db/*.*.csproj ./Asd.Db/
    
RUN dotnet restore


COPY Asd.Api/. ./Asd.Api/
COPY Asd.Db/. ./Asd.Db/


WORKDIR /app/Asd.Api

RUN dotnet publish -c Release -o out



FROM mcr.microsoft.com/dotnet/core/aspnet:3.1-bionic AS runtime
WORKDIR /app

COPY --from=build /app/Asd.Api/out ./
ENTRYPOINT ["dotnet", "Asd.Api.dll"]

Docker compose:

version: "3.8"
services:
  backend:    
    container_name: backend
    build:      
      context: ./backend/
      dockerfile: Dockerfile
    depends_on:  
       - db      
    ports:
      - "5000:80"
  db:
    image: "mcr.microsoft.com/mssql/server:2017-latest"
    container_name: sqlserver
    hostname: sqlserver
    environment:
      SA_PASSWORD: "ASDFGHJ12345"
      ACCEPT_EULA: "Y"
    restart: unless-stopped    
    ports:
      - "1433:1433"

Connection string in the appsettings.json

"ConnectionStrings": {
    "DefaultConnection": "Server=sqlserver;Database=AsdDB;user id=sa;password=ASDFGHJ12345;"
},

Answer 1

In the connection string, the Server had to be passed the following value:

Server=host.docker.internal;Database=AsdDB;user id=sa;password=ASDFGHJ12345%;

So now it works, it took 2 days of messing with it, but after I posted the question, I found the answer.