Search code examples
c#sql-serverdockerdocker-composeasp.net-core-webapi

Impossible to connect to my SQL Server docker database

I have a .NET 7 solution with an API Gateway: one API is the Gateway, and two others WebAPI1 and WebAPI2. These three APIS are dockerized projects.

I have another container - a SQL Server dockerized database.

My computer OS is Windows 11.

Despite all my efforts, I cannot connect my Web APIs to my database.

This is the error I get:

Microsoft.Data.SqlClient.SqlException: '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: SQL Network Interfaces, error: 44 - Could not compose Service Principal Name (SPN) for Windows Integrated Authentication. Possible causes are server(s) incorrectly specified to connection API calls, Domain Name System (DNS) lookup failure or memory shortage)'

The error is happening in my WebAPI1 project, in Program.cs, where a migration method is called:

var applicationConnectionString = builder.Configuration.GetConnectionString("ApplicationConnection");

builder.Services.AddDatabaseContext(applicationConnectionString!);
builder.Services.AddIdentityContext(applicationConnectionString!);

builder.Services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(applicationConnectionString));
builder.Services.AddDbContext<IdentityContext>(options => options.UseSqlServer(applicationConnectionString));

using (var serviceScope = app.Services.GetService<IServiceScopeFactory>()!.CreateScope())
{
    var dbContext = serviceScope.ServiceProvider.GetRequiredService<DatabaseContext>();
    dbContext.Database.Migrate(); // The error is happening here

    var identityContext = serviceScope.ServiceProvider.GetRequiredService<IdentityContext>();
    identityContext.Database.Migrate();
}

These are other pieces of code:

Connection string:

"ApplicationConnection": "Data Source=127.0.0.1,8001;Initial Catalog=MyDatabase;User Id={username}; Password={password};Trusted_Connection=true;TrustServerCertificate=True"

I also tried:

"ApplicationConnection": "Server=127.0.0.1,8001;Database=MyDatabase;User Id={username}; Password={password};Trusted_Connection=true;TrustServerCertificate=True"

Database context:

public class DatabaseContext : DbContext 
{ 
    public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options) { } 

    protected override void OnModelCreating(ModelBuilder modelBuilder) 
    { 
        EntityOne.Configure(modelBuilder); 
        EntityTwo.Configure(modelBuilder); 
        EntityThree.Configure(modelBuilder); 
        EntityFour.Configure(modelBuilder); 
    } 

    public DbSet<EntityOne> EntityOne { get; set; } 
    public DbSet<EntityTwo> EntityTwo { get; set; } 
    public DbSet<EntityThree> EntityThree { get; set; } 
    public DbSet<EntityFour> EntityFour { get; set; } 
    
    public async Task<int> SaveChangesAsync() 
    { 
        return await base.SaveChangesAsync(); 
    } 
}

WebApi1 Dockerfile:

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

FROM mcr.microsoft.com/dotnet/sdk:7.0 AS build 
ARG BUILD_CONFIGURATION=Release 
WORKDIR /src 
COPY ["WebAPIs.WebApi1/WebAPIs.WebApi1.csproj", "WebApi1.Calendar/"] 
COPY ["Core/Core.csproj", "Core/"] 
COPY ["Infrastructure/Infrastructure.csproj", "Infrastructure/"] 
COPY ["Infrastructure/Infrastructure.csproj", "Infrastructure/"] 
RUN dotnet restore "./WebAPIs.WebApi1/WebAPIs.WebApi1.csproj"
COPY . . 
WORKDIR "/src/WebAPIs.WebApi1" 
RUN dotnet build "./WebAPIs.WebApi1.csproj" -c $BUILD_CONFIGURATION -o /app/build 

FROM build AS publish 
ARG BUILD_CONFIGURATION=Release 
RUN dotnet publish "./WebAPIs.WebApi1.csproj" -c $BUILD_CONFIGURATION -o /app/publish /p:UseAppHost=true 

FROM base AS final 
WORKDIR /app 
COPY --from=publish /app/publish . 
ENTRYPOINT ["dotnet", "WebAPIs.WebApi1.dll"]

docker-compose.yml:

version: '3.4'

services:
  database:
    container_name: database
    image: mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04
    environment:
       - ACCEPT_EULA=Y
       - SA_DB_NAME= MyDatabase
       - SA_USER={username}
       - SA_PASSWORD={password}
    # volumes:
    #    - sqlserver_data:/var/opt/mssql
    ports:
       - 127.0.0.1:8001:1433
  
  webapis.gateway:
    container_name: webapis.gateway
    image: ${DOCKER_REGISTRY-}gateway
    build:
      context: .
      dockerfile: WebAPIs.Gateway/Dockerfile
    ports:
      - 127.0.0.1:5020:80
      - 127.0.0.1:7055:443
    depends_on:
      - database

  webapis.webapi1:
    image: ${DOCKER_REGISTRY-}webapisapi1
    build:
      context: .
      dockerfile: WebAPIs. WebApi1/Dockerfile
    ports:
      - 127.0.0.1:5234:80
      - 127.0.0.1:7207:443
    depends_on:
      - database

  webapis.webapi2:
    container_name: webapis. webapi2
    image: ${DOCKER_REGISTRY-} webapisapi2
    build:
      context: .
      dockerfile: WebAPIs.WebApi2/Dockerfile
    ports:
      - 127.0.0.1:5013:80
      - 127.0.0.1:7090:443
    depends_on:
      - database

docker-compose.override.yml:

version: '3.4'

services:
  webapis.gateway:
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
      - ASPNETCORE_HTTP_PORT=80
      - ASPNETCORE_HTTPS_PORT=443
    ports:
      - 127.0.0.1:5020:80
      - 127.0.0.1:7055:443
    volumes:
      - ${APPDATA}/Microsoft/UserSecrets:/root/.microsoft/usersecrets:ro
      - ${APPDATA}/ASP.NET/Https:/root/.aspnet/https:ro

  webapis.webapis1:
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
      - ASPNETCORE_URLS=https://+:443;http://+:80
    ports:
      - 127.0.0.1:5234:80
      - 127.0.0.1:7207:443
    volumes:
      - ${APPDATA}/Microsoft/UserSecrets:/root/.microsoft/usersecrets:ro
      - ${APPDATA}/ASP.NET/Https:/root/.aspnet/https:ro

  webapis.webapis2:
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
      - ASPNETCORE_URLS=https://+:443;http://+:80
    ports:
      - 127.0.0.1:5013:80
      - 127.0.0.1:7090:443
    volumes:
      - ${APPDATA}/Microsoft/UserSecrets:/root/.microsoft/usersecrets:ro
      - ${APPDATA}/ASP.NET/Https:/root/.aspnet/https:ro

I checked many things, including:

  • My containers are running in Docker Desktop.
  • I can access my database manually, through SSMS.
  • SQL Server remote connection to my server is allowed.
  • I looked at my firewall, SQL Server and Docker are allowed.

So now I don't know what to do, if anybody has some explanation or ideas, I'll be happy to read it!

Solution

  • Based on your advises and on my own discovery, I finally found a solution.

    • I deleted all the '127.0.0.1:' frome docker-compose.yml and docker-compose.override.yml
    • I also updated the connection string, using the DNS of the database container.
    • And the most important thing I discovered was to also modify the port number in the connection string from 8001 to 1433. 1433 is indeed the exposed port number of SQL Server and should be used in order for the application to have access to the database.

    This is what my connection string looks like now:

    "ApplicationConnection": "Server=database,1433;Database=MyDatabase;User Id={username}; Password={password};TrustServerCertificate=True"

    In my case, it also works like this:

    "ApplicationConnection": "Data Source=database,1433;Initial Catalog=MyDatabase;User Id={username}; Password={password};TrustServerCertificate=True"