I have a Docker Swarm of four Ubuntu 20.04 machines. I want to run many different apps in a replicated fashion. One of these apps is an API that reads from a remote SQL Server running on a named instance.

I am able to connect to SQL Server if I specify the host network mode but this is not ideal since I won't be able to map ports for other services that I want to run in the future. It also feels like a hack.

This is my docker-compose file named scapi-stack.yaml

version: '3.7'

services:
  sc-api:
    command: [ "--privileged" ]
    image: #private repo image
    deploy:
      replicas: 4
      restart_policy:
        condition: on-failure
    ports:
      - "51955:51955" #SQL Server instance port
      - "8443:443"
      - "8080:80"
    environment:
      - ASPNETCORE_URLS=http://+:80
      - ASPNETCORE_URLS=https://+:443

This is the command with which I run the service:

docker stack deploy --compose-file scapi-stack.yaml scapi

If I then do docker attach to a container and navigate to port 8080 I see the following error:

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)

I've tried specifying the --privileged command to no avail. I've also tried changing images from the main ones to bionic, focal and buster-slim. I've tried going down to .Net Core 3.1 as well and it made no difference. I have also run these two commands found in this answer:

sysctl net.ipv4.conf.all.forwarding=1
sudo iptables -P FORWARD ACCEPT

The only way I can get it to work is with the host network but that defeats the purpose.

The dockerfile for the image is this:

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

FROM mcr.microsoft.com/dotnet/sdk:5.0 AS build
WORKDIR /src
COPY ["SCAPI/SCAPI.csproj", "SCAPI/"]
COPY ["src/Infrastructure/Infrastructure.csproj", "src/Infrastructure/"]
COPY ["src/Application/Application.csproj", "src/Application/"]
COPY ["src/Domain/Domain.csproj", "src/Domain/"]
RUN dotnet restore "SCAPI/SCAPI.csproj"
COPY . .
WORKDIR "/src/SCAPI"
RUN dotnet build "SCAPI.csproj" -c Release -o /app/build

FROM build AS publish
RUN dotnet publish "SCAPI.csproj" -c Release -o /app/publish

FROM base AS final
#Enable connections with TLS 1.0
RUN sed -i 's/MinProtocol = TLSv1.2/MinProtocol = TLSv1/g' /etc/ssl/openssl.cnf
RUN sed -i 's/MinProtocol = TLSv1.2/MinProtocol = TLSv1/g' /usr/lib/ssl/openssl.cnf
RUN sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=1/g' /etc/ssl/openssl.cnf
RUN sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=1/g' /usr/lib/ssl/openssl.cnf
WORKDIR /app
COPY --from=publish /app/publish .
ENTRYPOINT ["dotnet", "SCAPI.dll"]

What am I missing? How can I specify the ports to enable comms to SQL Server without using the host network?

Update

The SQL server I am trying to connect to is on a Windows machine outside of the swarm. I can connect to it if I remove port mapping and specify 'host' networking in my compose file:

version: '3.7'

services:
  sc-api:
    command: [ "--privileged" ]
    image: #private repo image
    deploy:
      replicas: 4
      restart_policy:
        condition: on-failure
    environment:
      - ASPNETCORE_URLS=http://+:80
      - ASPNETCORE_URLS=https://+:44
    networks:
      - host

networks:
  host:
    name: host
    external: true

But that has drawbacks as described in the official Docker documentation:

If you use the host network mode for a container, that container’s network stack is not isolated from the Docker host (the container shares the host’s networking namespace), and the container does not get its own IP-address allocated. For instance, if you run a container which binds to port 80 and you use host networking, the container’s application is available on port 80 on the host’s IP address.

Leaving this for posterity

So after close to 60 hours spent trawling the internet looking for clues and trying a million different things, this has finally been resolved. I even switched to a Kubernetes cluster spun up on the same machines to no avail - I had the same issue.

My network-guru colleague finally got me to install Wireshark on one of the linux boxes and he discovered that the traffic was going out of the containers/pods, through the linux box interface and to the SQL server, but was not coming back.

After some time he discovered that the IP addresses coming out of swarm/kubernetes weren't masqueraded and the core network switch didn't know how to return traffic back to the containers/pods.

These linux boxes are virtual machines.

A quick sudo iptables --append POSTROUTING --table nat --out-interface ens160 --jump MASQUERADE where 'ens160' is the network interface - and voila! All good.

This command translates all the container/pod IP addresses to the IP address of the box and vice-versa for all outbound traffic.