question

AnirbanGoswami-8125 avatar image
0 Votes"
AnirbanGoswami-8125 asked yogyogi answered

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

Hi all,
I am using windows 10 pro with docker host and my docker version as below
46542-dockerversion.jpg

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


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,

sql-server-generaldotnet-csharpdotnet-ad
dockerversion.jpg (51.6 KiB)
folderstrucrure.jpg (26.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AnirbanGoswami-8125,

Just following up. Is there any update on this case?
Best regards,

0 Votes 0 ·

Changed and tried again as suggested and I also looked into api container logs , ERROR is coming as above.
I also found below reference thread for 'microsoft/mssql-server-linux:2017-latest' or Others linux container version of 2017 and it is saying that for Windows 10 Pro 1809+ as host , it gives a fatal error and possible not able to connect with another container.. or mounting purpose

https://github.com/microsoft/mssql-docker/issues/441

I am using host as win 10- Pro , Version 10.0.18362 -Build 18362 and docker-desktop,version:2.3.0.4(46911)-Engine:19.03.12 that why.. containers r creating successfully at OS/Arch: linux/amd64 environment but api container not able to connect with DB container.

Any suggestion pls

Thanks,

0 Votes 0 ·
OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered AnirbanGoswami-8125 commented

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.



· 10
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you @OlafHelper-2800 for replay.
2 containers are created but api container is failing to connect to sql-db container.

should i attach Dockerfile and modified 'docker-Compose.yaml' file for correction?
Pls, help me. Thanks,

0 Votes 0 ·

Olaf, Anirban defines port 1401 to be the one to connect to container, so that is correct.

Anirban, are you able to connect to the container directly from the Docker host with SQLCMD or SSMS?

0 Votes 0 ·

Thank you @ErlandSommarskog for replay.
yes , i changed port (1401) as below at Powershell and able to connect by SSMS from Host.
But, I am unable to connect with sql-db container from api container. Restful api app made with dotnetcore 2.2 and defined sql-db connection at 'appsettings.json' file as below ..

 {
    "AllowedHosts": "",
    "ConnectionStrings": "Data Source=ms-sql-server,1401,1433;Initial Catalog=Colours;User Id=SA;Password=Pa$$w0rd2019;"
 }

I am using 'Microsoft.EntityFrameworkCore' ORM of version 1.0.1 for sql db data retrieving.
i am attached 'docker-Compose.yaml' file for correction purpose.
46934-docker-compose.txt
Error msg is coming as above from api container..

Pls , help me..

0 Votes 0 ·
DanGuzman avatar image DanGuzman AnirbanGoswami-8125 ·

Change Data Source=ms-sql-server,1401,1433 to Data Source=ms-sql-server,1401. Only the single exposed port number (1401) need be specified.

0 Votes 0 ·
Show more comments
BenMiller-DBAduck avatar image
0 Votes"
BenMiller-DBAduck answered AnirbanGoswami-8125 commented

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).

· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you anonymous user for replay and help.
Still, i am struggling..below is db connection string at appsetting.json fie as..

 {
   "AllowedHosts": "*",   
   "ConnectionStrings": "Server=ms-sql-server;Initial Catalog=Colours;User Id=SA;Password=Pa$$w0rd2019;Integrated Security=False;"
 }

Startup.cs file have below code as..

 [47320-startupcs.txt][1]

Any help or guidance will appreciate..
Thanks,


[1]: /answers/storage/temp/47320-startupcs.txt

0 Votes 0 ·
startupcs.txt (982 B)
DanGuzman avatar image DanGuzman AnirbanGoswami-8125 ·

Are you getting the same "A network-related or instance-specific error..." with this connection string or some other error? One thing to watch out for is that the "$$" characters in the password may cause issues in Linux containers due to shell interpreting it as a process id variable instead of the literal "$$". You might try using a different password in that case.

0 Votes 0 ·

Thank you @DanGuzman for replay and help.
I changed pwd both yml file, appsetting.json fie and run
docker-compose up --build command at powershell . Bellow Error is coming as

 "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) ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (00000005, 6): No such device or address
 colour-api_1     |    at System.Net.Dns.InternalGetHostByName(String hostName)
 colour-api_1     |    at System.Net.Dns.GetHostAddresses(String hostNameOrAddress)    "

below is created containers..
47423-dockerversion.jpg
Thanks,





0 Votes 0 ·
dockerversion.jpg (53.8 KiB)
Show more comments

Thank you anonymous user for replay.

0 Votes 0 ·

Any update on solutions or should i deploy 2 containers to azure app server for Linux and test ?

Thanks

0 Votes 0 ·
Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered AnirbanGoswami-8125 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @Criszhan-msft and Apologize for late response.
Tested existing code with 'microsoft/mssql-server-linux:2019-latest' DB Linux images but same error is coming..
Containers were creating successfully at OS/Arch: linux/amd64 environment but API container not able to connect with 'microsoft/mssql-server-linux:2019-latest' sql-DB container.

I am using Dev envr (host) as win 10- Pro , Version 10.0.18362 -Build 18362 and docker-desktop,version:2.3.0.4(46911)-Engine:19.03.12

Git bug report repo as below
https://github.com/microsoft/mssql-docker/issues/441

Thanks,

0 Votes 0 ·
NerminkoOmani-5690 avatar image
0 Votes"
NerminkoOmani-5690 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

yogyogi avatar image
0 Votes"
yogyogi answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.