Docker sql Image : Cannot open database"xyz"

Question

I have been getting following error while trying to setup sql docker containerized for my application:-

An unhandled exception occurred while processing the request.
SqlException: Cannot open database "xyzDatabase" requested by the login. The login failed.Login failed for user 'sa'.

WebMVC: Docker file configuration:-

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/Web/WebUI/WebUI.csproj", "src/Web/WebUI/"]
COPY ["src/Core/Application/Application.csproj", "src/Core/Application/"]
COPY ["src/Infrastructure/Persistence/Persistence.csproj", "src/Infrastructure/Persistence/"]
RUN dotnet restore "src/Web/WebUI/WebUI.csproj"
COPY . .
WORKDIR "/src/src/Web/WebUI"
RUN dotnet build "WebUI.csproj" -c Release -o /app

FROM build AS publish
RUN dotnet publish "WebUI.csproj" -c Release -o /app

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

Startup:-

 public static IServiceCollection AddSqlServerDatabase(this IServiceCollection services, IConfiguration configure)
        {
            services.AddEntityFrameworkSqlServer()
                   .AddDbContext<CatalogContext>(options =>
                   {
                       options.UseSqlServer(configure["ConnectionString"],
                           sqlServerOptionsAction: resilient =>
                           {
                               //resilient.MigrationsAssembly(typeof(Startup).GetTypeInfo().Assembly.GetName().Name);
                               resilient.EnableRetryOnFailure(maxRetryCount: 10, maxRetryDelay: TimeSpan.FromSeconds(30), errorNumbersToAdd: null);
                           });

                   }, ServiceLifetime.Scoped);

            services.AddEntityFrameworkSqlServer()
                    .AddDbContext<AppIdentityDbContext>(options =>
                    {
                        options.UseSqlServer(configure["ConnectionString"],
                        sqlServerOptionsAction: resilient =>
                        {
                            resilient.EnableRetryOnFailure(maxRetryCount: 10, maxRetryDelay: TimeSpan.FromSeconds(30), errorNumbersToAdd: null);
                        });
                    }, ServiceLifetime.Scoped);

            services.AddIdentity<ApplicationUser, IdentityRole>()
                    .AddEntityFrameworkStores<AppIdentityDbContext>()
                    .AddDefaultTokenProviders();

            return services;
        }

docker-compose.yml file:-

version: '3.4'

services:

  sql.data:
   image: microsoft/mssql-server-linux:2017-latest

  webui:
    image: ${DOCKER_REGISTRY-}webui
    build:
      context: .
      dockerfile: src/Web/WebUI/Dockerfile
    depends_on:
      - sql.data

docker-compose.override.yml

version: '3.4'

services:

  sql.data:
   environment:
    - SA_PASSWORD=Pass@word
    - ACCEPT_EULA=Y
   ports:
    - "5433:1433"

  webui:
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
      - ASPNETCORE_URLS=http://0.0.0.0:80
      - ConnectionString=Server=sql.data;Database=eWebShopDatabase;User=sa;Password=Pass@word

    ports:
      - "5107:80"
    volumes:
      - ${APPDATA}/Microsoft/UserSecrets:/root/.microsoft/usersecrets:ro

I know what does it mean by Volumes but I really don't understand what it tries to say in the line:-

Volumes: ${APPDATA}/Microsoft/UserSecrets:/root/.microsoft/usersecrets:ro,

It is added automatically when we add "Container Orchestrator Support" from the project.

I really don't understand whether I need to create a user name prior to attach the sql image container with asp.net core app or does it create by default based on value as implemented in the code.

appsettings.json file:-

{
  "ConnectionString": "Server=tcp:127.0.0.1,5433;Database=xyzDatabase;User=sa;Password=Pass@word;"
}

docker build and created container image file but it does not run or could not seed the database as it fail to login may be user not present or cant create.

Someone might have faced such issue and hope to have some insights about the issue!

Thanks!

Answer 1

SA_PASSWORD is not the same thing as MSSQL_SA_PASSWORD. Try this docker-compose.override.yml instead:

version: '3.4'

services:

  sql.data:
   environment:
    - MSSQL_SA_PASSWORD=Pass@word
    - ACCEPT_EULA=Y
   ports:
    - "5433:1433"

  webui:
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
      - ASPNETCORE_URLS=http://0.0.0.0:80
      - ConnectionString=Server=sql.data;Database=eWebShopDatabase;User=sa;Password=Pass@word

    ports:
      - "5107:80"
    volumes:
      - ${APPDATA}/Microsoft/UserSecrets:/root/.microsoft/usersecrets:ro

REF: Configure SQL Server container images on Docker