Unable to connect to SQL Server from a docker container

Question

We have an ASP.NET Core 2.2 application that worked with its backend database hosted on a separate server perfectly. All CRUD operations worked flawlessly. We dockerized the application but we started getting the following error message:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

The docker compose file looks like the following code:

version: '3.4'

services:
  services.webapi:
    image: ${DOCKER_REGISTRY-}serviceswebapi
    build:
      context: .
      dockerfile: Services.WebApi\Dockerfile
      network: host

What may be wrong in this configuration hindering the application to communicate with the SQL Server instance?

UPDATE 1: the SQL Server is not dockerized.

UPDATE 2: The application reads the SQL connection string from its appsettings.json file. This is the connection string after renaming the server name and credentials:

Server=WIN-MSSQL1\\MSSQLSERVER2017;Initial Catalog=ServiceDB;User ID=cruduser;Password=foo;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

Answer 1

In a normal situation you could fix by adding the sql hostname in the /etc/hosts file of the container via composer file like this:

version: '3.4'

services:
  services.webapi:
    image: ${DOCKER_REGISTRY-}serviceswebapi
    build:
      context: .
      dockerfile: Services.WebApi\Dockerfile
      network: host
    extra_hosts:
      - "WIN_MSSQL1:192.168.0.1"

Unfortunately due this issue adding the extra_hosts in the composer file will not work, I hope they fix the issue soon.

So to let connection work substitute the hostname with the ip address of SQL server:

from this:

Server=WIN-MSSQL1\\MSSQLSERVER2017;Initial Catalog=ServiceDB;User ID=cruduser;Password=foo;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

to this:

Server=192.168.0.1\\MSSQLSERVER2017;Initial Catalog=ServiceDB;User ID=cruduser;Password=foo;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False