how to pass sql DB container connection string to api container.

Question

Hi all,
I am using windows 10 pro with docker host and my docker version as below

I created a 'docker-Compose.yaml' file for creating a api container and sql db container and below is 'docker-Compose.yaml' file as

Below is my 'docker-Compose.yaml' file..

version: '3.7'  
services:  
ms-sql-server:  
image: 'microsoft/mssql-server-linux:2017-latest'  
ports:  
- '1401:1433'  
environment:  
SA_PASSWORD: Pa$$w0rd2019  
ACCEPT_EULA: Y  
MSSQL_PID: Developer  
colour-api:  
build:  
context: ColourAPI  
dockerfile: Dockerfile  
environment:  
- ASPNETCORE_ENVIRONMENT=Development  
- ConnectionStrings_DefaultConnection=Server=localhost,1401;database=Colours;User Id=sa;Password=Pa$$w0rd2019  
ports:  
- '8080:80'  
depends_on:  
- ms-sql-server  

I run 'docker-compose up --build' command at powershell and 2 containers were created successfully but api container hv a ERROR message like
"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: 35 - An internal exception was caught)".

Pls help me on how to pass Db container connection string to api container.

Thanks,

Answer 1

ConnectionStrings_DefaultConnection=Server=localhost,1401

The default port for a SQL Server standard instance is 1433, not 1401. And for a local connection you don't need the port at all, remove it.

Answer 2

When connecting from container to container you would use 1433 as the port as you are not interacting with the host but the container.

So the connection string would connect to either the IP of the container (from docker inspect containerid) or to the container name in this case ms-sql-server.

Data Source=ms-sql-server,1433 (or leave off the port).

Answer 3

Hi,

So your current problem is that you can connect to the sql-db container directly from the Docker host with SSMS,but unable to connect with sql-db container from api container. Have you tried the latest SQL 2019 image.

Check the following similar posts if help:
https://stackoverflow.com/questions/50817995/how-to-communicate-between-two-docker-containers-mssql-and-net-core-app-got-c
https://stackoverflow.com/questions/52926064/how-to-connect-to-sql-sever-docker-container-from-another-container
https://stackoverflow.com/questions/58217700/connecting-to-a-sql-server-database-running-in-a-docker-container-from-another-d

Answer 4

I'm not big expert but maybe you can try to rewrite this make a network inside docker-compose file and add network property with that name to both sql server and API, even if they are in same container logic behind docker is isolation.. so you need to specify network on your container..

Also what i would do just to be sure, i would give name to connection string in both appsetting and yml so you're sure that it will overwrite it..
Also idk why you from yml file call localhost and from appsetting name of server, maybe it is better to put in yml file name of sql server which you're creating.

Sorry for this late answer probably you solved it, but i came across this problem now so i found your post.

Answer 5

It seems you have 2 Docker Containers:

1. For Web API
2. For SQL Server

You need Web API Container to Access SQL Server container for doing database operations. Here you need Docker Compose. You can create it with Visual Studio itself. It's code should be as shown below:

version: '3.4'

services:
  dockercrud:
    image: ${DOCKER_REGISTRY-}dockercrud
    build:
      context: .
      dockerfile: WebAPI/Dockerfile

  sqldata:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - SA_PASSWORD=yourpassword
      - ACCEPT_EULA=Y
    ports:
      - "1433:1433"

You reference SQL Server with sqldata service and so in your connection string you can refer it as:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=sqldata;Initial Catalog=MovieDB;Persist Security Info=True;User ID=SA;Password=yourpassword"
  }
}

Reference - CRUD Operations in ASP.NET Core and SQL Server with Docker