CODEWITHSUNDEEP
sql-serverdockerasp.net-core.net-core
Vaccano
Vaccano

Reputation: 82361

ASP.NET Core Web API Service cannot connect to Docker SQL Server on same network

I have a ASP.NET Core v3 Web API service. It runs and connects just fine to a normal Windows hosted SQL Server instance. But when I try to connect it to a Docker Linux hosted SQL Server Instance it fails.

Here are some details:

Here is my database connection string:

"Server=localhost;Database=MyDatabase;User Id=MySqlUser;Password=MyPassword"

I also tried making an entry in my hosts file of localsql set to 127.0.0.0. Both localhost and localsql work fine to connect to the docker database using SSMS.

This is the actual 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: TCP Provider, error: 40 - Could not open a connection to SQL Server)

When I try using localsql from my hosts file, it has an inner exception with this message "Name or service not known"

Upvotes: 4

Views: 2877

Answers (2)

Emil
Emil

Reputation: 6893

Answer from Markus perfectly fine but I would like to extend or make it more comprehensive what he wrote. Basically it is about default Bridging and User defined Bridging. If use default bridging you can also achieve this by linking containers. See this article for more information

you can also create a User defined Bridge using

$ docker network create my-net

Then when you create new container like Markus' example code. you can give this bridge name in your create command.

$ docker create --name my-nginx \
  --network my-net \
  --publish 8080:80 \
  nginx:latest

If you have already created the container, you can use this command below to connect your created bridge to existing container. Do this for sql server and api (sql1 is the name of the sql server). Then you dont need to defined localhost in your connection string as they will be in the same network.

$ docker network connect my-net sql1

It is pretty annoying actually every time you stop docker and redeploy(debug) containers, you may need to go through this steps by

  • first disconnect: docker network disconnect my-net sql1
  • remove network: docker network rm my-net
  • connect again: docker network connect my-net sql1

Upvotes: 1

Markus
Markus

Reputation: 22456

The connection string points to localhost. Localhost in a container refers to the container itself, so that the ASP.NET Core Container tries to find the SQL Server on the same container.

Instead of localhost, use the name of the SQL Server container in the connection string. In order for this to work, you need to create a user-defined network bridge that both containers are connected to:

docker network create sql-server-test

When running the containers, you need to specify the network that the containers are connected to, like so:

# Please note the --name and --network parameters
docker run -e ACCEPT_EULA=Y -e 'SA_PASSWORD=<PWD>' -d --name sql-server --network sql-server-test mcr.microsoft.com/mssql/server:2019-latest
# Please note the --network parameter
docker run -d -p 8889:80 --name aspnet-core --network sql-server-test aspnet-core

As both containers are now connected to the same network, you can use the name of the Sql Server container (in the same sql-server) in the connection string and connect to the Sql Server using this connection string: 

"Data Source=sql-server;Initial Catalog=master;User ID=sa;Password=<PWD>"

For details on Docker networks and on other options on how to setup communication between containers, see this link.

Upvotes: 4

Related Questions