skip to Main Content

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!

2

Answers


  1. Chosen as BEST ANSWER

    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"
    

  2. If you run your app in the container 127.0.0.1 will mean the container itself which clearly does not have a database running. Since your docker compose file places all the container in the same network (the default one) you can use service name for service discovery/IP address resolution. So try something like the following in your connection strings:

    "ApplicationConnection": "Data Source=database,8001;...."
    

    Also I recommend to use the environment variables to pass the ApplicationConnection for docker compose case:

    environment:
      - ConnectionStrings__ApplicationConnection=Data Source=database,8001;....
    

    See also:

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search