EF Core SQL Database connection error in a Docker container application-CodePudding

I have a simple Entity Framework Core (5.0.11) application that connects with the localhost instance of the SQL Server. It works fine when I run from IDE but gives SQL connection error when I run from Docker Container.

I'm not using a stand alone container for the SQL server, rather connecting to the localhost on my machine.

Connection string I'm using is as follow

  "ConnectionStrings": {
    "DefaultConnectionString": "Server=localhost;Database=LOCALDatabase;User id=userId;password=password"
  }

Following is the docker container error

"EventId":20004,"LogLevel":"Error","Category":"Microsoft.EntityFrameworkCore.Database.Connection","Message":"An error occurred using the connection to database \u0027LOCALDatabase\u0027 on server \u0027localhost\u0027.","State":{"Message":"An error occurred using the connection to database \u0027LOCALDatabase\u0027 on server \u0027localhost\u0027.","database":"LOCALDatabase","server":"localhost","{OriginalFormat}":"An error occurred using the connection to database \u0027{database}\u0027 on server \u0027{server}\u0027."

I have tested the containerized application with dummy data (Not from the DB) and it is working fine so there is no issue in port mapping or accessibility of the containerized application.

Docker build and run commands are as follow

docker build -t test-api:v1 .

docker run -it -p 7277:80 --name testapi test-api:v1

Update

Following are the code files

//Data Access Layer Project
namespace DAL.Repositories
{
    public interface ITable1Repository
    {
        Task<IEnumerable<Dbtable1>> GetAsync();
    }
}

public class Table1Repository : ITable1Repository
{
    private readonly LOCALDbContext _dbContext;
    public Table1Repository(LOCALDbContext dbContext)
    {
        _dbContext = dbContext;
    }
    public async Task<IEnumerable<Dbtable1>> GetAsync()
    {
        return await _dbContext.Dbtable1s.ToListAsync();
    }
}

//Database entity
namespace DAL.Entities
{
    [Table("DBTable1")]
    public partial class Dbtable1
    {
        [Key]
        public int Column1 { get; set; }
        [StringLength(50)]
        public string Column2 { get; set; }
        [StringLength(50)]
        public string Column3 { get; set; }
    }
}

//DbContext
public partial class LOCALDbContext : DbContext
{
    public LOCALDbContext()
    {
    }

    public LOCALDbContext(DbContextOptions<LOCALDbContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Dbtable1> Dbtable1s { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasAnnotation("Relational:Collation", "Latin1_General_CI_AS");

        modelBuilder.Entity<Dbtable1>(entity =>
        {
            entity.Property(e => e.Column1).ValueGeneratedNever();
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}


//API Project
//Controller
namespace API.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class TestController : ControllerBase
    {
        private readonly ITable1Repository _table1Repository;
        public TestController(ITable1Repository table1Repository)
        {
            _table1Repository = table1Repository;
        }

        // GET: <TestController>
        [HttpGet]
        public async Task<IEnumerable<Dbtable1>> Get()
        {
            return await _table1Repository.GetAsync();
        }
    }
}

//Program.cs
using DAL.Data;
using DAL.Repositories;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
builder.Services.AddSwaggerGen(c =>
{
    c.SwaggerDoc("v1", new() { Title = "API", Version = "v1" });
});

var connectionString = builder.Configuration.GetConnectionString("DefaultConnectionString");
builder.Services.AddDbContext<LOCALDbContext>(options => options.UseSqlServer(connectionString));

builder.Services.AddScoped<ITable1Repository, Table1Repository>();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "API v1"));
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();


//appsettings.json
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnectionString": "Server=localhost;Database=LOCALDatabase;User id=userid;password=password"
  }
}

//appsettings.Docker.json
{
  "ConnectionStrings": {
    "DefaultConnectionString": "Server=host.docker.internal;Database=LOCALDatabase;User id=userid;password=password"
  }
}

//DockerFile
FROM mcr.microsoft.com/dotnet/aspnet:6.0 AS base
WORKDIR /app
EXPOSE 80
EXPOSE 443

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

COPY *.sln ./
COPY ./src/API/API.csproj ./src/API/
COPY ./src/DAL/DAL.csproj ./src/DAL/

RUN dotnet restore
COPY . .

RUN dotnet publish -c Release -o out

FROM base
WORKDIR /app

COPY --from=build /src/out .
ENTRYPOINT ["dotnet", "API.dll"]

docker build and run commands docker build -t test-api:v1 . docker run -it -p 7277:80 -e "ASPNETCORE_ENVIRONMENT=Docker" --name testapi test-api:v1

I have tested the containerized application with some local data (Non Db) and it works fine but SQL connection is causing the issue.

Here's my solution folder structure

Following is the DB table script

USE [LOCALDatabase]
GO
/****** Object:  Table [dbo].[DBTable1]    Script Date: 04/11/2021 13:50:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBTable1](
    [Column1] [int] NOT NULL,
    [Column2] [nvarchar](50) NULL,
    [Column3] [nvarchar](50) NULL,
 CONSTRAINT [PK_DBTable1] PRIMARY KEY CLUSTERED 
(
    [Column1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[DBTable1] ([Column1], [Column2], [Column3]) VALUES (1, N'Richard', N'King')
INSERT [dbo].[DBTable1] ([Column1], [Column2], [Column3]) VALUES (2, N'Stuart', N'Philip')

CodePudding user response：

The problem is that when you specify localhost in the connection string, it resolves to the container not the outside host machine, in order to fix that use host.docker.internal instead of localhost and 127.0.0.1

Also in order to keep your existing method of running your API from outside docker operational, you could create a new app settings file :

appSettings.Docker.json

"ConnectionStrings": {
    "DefaultConnectionString": "Server=host.docker.internal;Database=LOCALDatabase;User id=userId;password=password"
  }

and change your docker cli to :

docker build -t test-api:v1 .

docker run -it -p 7277:80 -e "ASPNETCORE_ENVIRONMENT=Docker" --name testapi test-api:v1

sources:

1433 is the default port used by the SQL Server. Double click TCP/IP and go to the IP Addresses tab and check if 1433 port is there for TCP Port under IPAAll section.
Restart SQL Server service from Services section
Allow port 1433 to be accessible through Windows Firewall by following the instructions in the following link https://www.windowscentral.com/how-open-port-windows-firewall