I have an ASP.NET Core 6 Web API running in docker and I want to connect to local SQL Server database (not dockerized!), but I'm unable to do so. Connecting to a database on remote server by IP works fine, but using connection string like

var dbHost = "COM-3195\\IMRANMSSQL";
var dbName = "CustomersDb";
var dbPassword = "prg@321654";
var connectionString = $"Data Source={dbHost};Initial Catalog={dbName};User Id=sa;Password={dbPassword}";

My dockerfile:

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

FROM mcr.microsoft.com/dotnet/sdk:6.0 AS build
WORKDIR /src
COPY ["CustomerWebService/CustomerWebService.csproj", "CustomerWebService/"]
RUN dotnet restore "CustomerWebService/CustomerWebService.csproj"
COPY . .
WORKDIR "/src/CustomerWebService"
RUN dotnet build "CustomerWebService.csproj" -c Release -o /app/build

FROM build AS publish
RUN dotnet publish "CustomerWebService.csproj" -c Release -o /app/publish /p:UseAppHost=false

FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
ENTRYPOINT ["dotnet", "CustomerWebService.dll"]

My Docker-Compsoe.yml

services:
  customerwebservice:
    image: ${DOCKER_REGISTRY-}customerwebservice
    build:
      context: .
      dockerfile: CustomerWebService/Dockerfile
    extra_hosts:
      - "COM-3195\\IMRANMSSQL:<IP>"

My application is not connecting to the database, and showing this in the log:

info: Microsoft.Hosting.Lifetime[14]
      Now listening on: https://[::]:443
info: Microsoft.Hosting.Lifetime[14]
      Now listening on: http://[::]:80
info: Microsoft.Hosting.Lifetime[0]
      Application started. Press Ctrl C to shut down.
info: Microsoft.Hosting.Lifetime[0]
      Hosting environment: Development
info: Microsoft.Hosting.Lifetime[0]
      Content root path: /app/
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 6.0.5 initialized 'CustomerDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer:6.0.5' with options: None
fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
      An error occurred using the connection to database 'master' on server '<IP>,1433'.
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)

It is working fine if I am running without docker, if I do like this in my composer.yml file

version: '3.4'

networks:
  backend:
services:
  customerdb:
    container_name: customer-db
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=prg@321654
    networks:
      - backend
    ports:
      - 8001:1433 
  customerwebapi:
    container_name: cutomer-api
    image: ${DOCKER_REGISTRY-}customerwebapi
    build:
      context: .
      dockerfile: CustomerWebAPI/Dockerfile
    networks:
      - backend
    ports:
      - 8002:80 
    environment:
      - DB_HOST= customerdb
      - DB_NAME= CustomersDb
      - DB_SA_PASSWORD=prg@321654

It is working fine but it's running on local,8001 in SQL Server, but I want my local SQL Server to add.

Please help, I just started learning Dockering.

Please help me connect to SQL Server database (not dockerized) from my Docker image

CodePudding user response：

To access something on the host, you can add a name for the host-gameway in the extra_hosts section. The usual thing is to call it host.docker.internal, so we'll do that here as well.

Then you need to change the database hostname to host.docker.internal and you should be able to connect.

I'm a little confused as to how you specify the database host name in your program. In the C# snippet, you show a hard-coded value. But in the docker-compose file where you run the database in a container, you set an environment variable. The 'nice' thing to do is to handle it via an environment variable, so I'll show that solution here

services:
  customerwebservice:
    image: ${DOCKER_REGISTRY-}customerwebservice
    build:
      context: .
      dockerfile: CustomerWebService/Dockerfile
    extra_hosts:
      - host.docker.internal:host-gateway
    environment:
      - DB_HOST=host.docker.internal

CodePudding user response：

You have the two following solutions:

1. Get the IP from your host as part of the virtual network that docker has created.

   • Use commands like docker inspect <container_id>, docker network ls and docker network inspect <network_id> to receive the IP address of the "Gateway"
   • This IP can be found with docker network inspect ... at IPAM > Config > Gateway

2. Use the IP of your host e.g. from eth0. I am not 100% sure, but you should also be able to use at least the IP-Adress from your main network interface.

Just as a general information, even if your container and the DB are running on the same host, you cannot use localhost as this is being resolved to different systems (once for the container, once for the host itself)