Can't connect to docker sql server from NET Core 2.2 Web API

Question

While collecting knowledge of coding .NET Core Web API in combination with docker, I came across an error that I can't solve. I already did research on the internet, but somehow none of the provided solutions worked for me.

I have a web service called Catalog.API (similar to the eShopOnContainers project from Microsoft), where I have a controller called CatalogController.

This is the code from the controller:

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Catalog.API.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace Catalog.API.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class CatalogController : ControllerBase
    {
        private readonly CatalogItemContext _context;

        public CatalogController(CatalogItemContext context)
        {
            _context = context;
        }

        public async Task<List<CatalogItem>> GetItemsAsync()
        {
            var items = await _context.CatalogItems.Where(model => model.Id < 10).ToListAsync();
            return items;
        }
    }
}

As you can see, I only have one method that should return all items from an external database, that have an id smaller than 10.

The database is added in the Startup.cs:

namespace Catalog.API
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<CatalogItemContext>(builder =>
            {
                builder.UseSqlServer(Configuration.GetConnectionString("Default"));
            });

        services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
                app.UseHsts();
            }

            app.UseHttpsRedirection();
            app.UseMvc();
        }
    }
}

The appsettings.json looks like this:

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "Default": "Server=localhost,1433;Initial Catalog=Test.Services.CatalogDb;User Id=sa;Password=Pass@word" 
  }
}

To build this web service, I created a docker file that is called from the docker-compose.yml:

FROM mcr.microsoft.com/dotnet/core/aspnet:2.2 AS base
WORKDIR /app
EXPOSE 80

FROM mcr.microsoft.com/dotnet/core/sdk:2.2 AS build
WORKDIR /src

COPY src/Services/Catalog/Catalog.API/Catalog.API.csproj /src/csproj-files/

WORKDIR ./csproj-files
RUN dotnet restore

WORKDIR /src

COPY . .
WORKDIR /src/src/Services/Catalog/Catalog.API/
RUN dotnet publish -c Release -o /app

FROM build AS publish

FROM base as final
WORKDIR /app
COPY --from=publish /app .
ENTRYPOINT ["dotnet", "Catalog.API.dll"]

The docker-compose.yml looks like this:

version: '3.4'

services:

  sqldata:
    ports:
      - 1433:1433
    image: mcr.microsoft.com/mssql/server:2017-latest-ubuntu
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=Pass@word

  catalog.api:
    ports:
      - 80:80
    build:
      context: .
      dockerfile: src/Services/Catalog/Catalog.API/Dockerfile
    depends_on:
      - sqldata

As already mentioned, in addition to the Catalog.API web service, I am also running a SQL Server database inside a docker container. For development purposes, I am connecting to this SQL Server from DataGrip using localhost or my machine ip as the host address (localhost or 192.168.2.101). Both ways work fine and I can establish a connection inside DataGrip without a problem.

My problem is that I can not connect to SQL Server from inside the docker container in my CatalogController, running on localhost:80. Normally, when I call localhost:80/api/catalog, I should get all items out of the database (see the controller at the beginning), but somehow, I get this error:

fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
An error occurred using the connection to database 'Test.Services.CatalogDb' on server 'localhost,1433'.
System.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)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
--- End of stack trace from previous location where exception was thrown ---

at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken) ClientConnectionId:00000000-0000-0000-0000-000000000000

I already tried to use serveral ips (localhost, my machine ip + the ip out of docker inspect), but none is working.

Server=192.168.2.101,1433;Initial Catalog=Test.Services.CatalogDb;User Id=sa;Password=Pass@word

I am open for every help, that can solve my problem. Thanks in advance.

Answer 1

The SQL Server instance is not on localhost. In the context of the web app, localhost is the container the web app is running in, which is not the same container that SQL Server is in.

When using Docker Compose, the service names end up as hostnames, which means that you can simply connect to sqldata,1433.

Adding the service name instead of the IP works, but whenever i migrate the database using "dotnet ef migrations add xyz" and "dotnet ef database update", i get an error. It says, server not found or not accessible.. So now i can connect, but cannot use dotnet ef.

The hostname is only valid within the vlan that your docker containers are running in, which is not the same vlan your desktop machine is in. To access the SQL Server container, you must either docker container exec into one of the containers and run the commands from there (where the sqldata hostname will exist) or you must use the IP address of the container, which you can get by running docker container ls to get the container id (guid) and then run docker inspect {container id}. The IP address will be in the NetworkSettings section of the resulting JSON.