ASP.NET与SQL Server容器部署时sa用户登录失败问题排查与解决

初始部署配置 #

我有一个ASP.NET Web API应用，开发阶段使用SQL Server容器，计划通过Docker Compose将应用和数据库容器一同部署。

初始docker-compose.yaml #

version: '2.4'

services:
  db:
    image: "mcr.microsoft.com/mssql/server:2022-latest"
    ports:
      - "1433:1433"
    environment:
      - SA_PASSWORD=Testpass123!@#
      - ACCEPT_EULA=Y
    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Testpass123!@#' -Q "SELECT 1"
      interval: 10s
      retries: 15
    networks:
      - mutual-network
  
  api:
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8080:80"
    depends_on:
      db:
        condition: service_healthy
    networks:
      - mutual-network

networks:
  mutual-network:

初始Dockerfile #

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

FROM mcr.microsoft.com/dotnet/sdk:7.0 AS build
WORKDIR /src
COPY ["update.csproj", "./"]
RUN dotnet restore "./update.csproj"
COPY . .
RUN dotnet build "update.csproj" -c Release -o /app

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

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

连接字符串 #

"Server=172.18.0.2;Database=testDB;User=sa;Password=Testpass123!@#;TrustServerCertificate=True;"

第一次错误 #

调用API接口时出现登录失败错误：

Login failed for username 'sa'.

更新配置后的情况 #

发现SA_PASSWORD与MSSQL_SA_PASSWORD均可用，最初compose文件使用的是后者，之后修改了配置：

更新后的docker-compose.yaml #

version: '2.4'

services:
  db:
    image: "mcr.microsoft.com/mssql/server:2022-latest"
    ports:
      - "1433:1433"
    environment:
      - MSSQL_SA_PASSWORD=Testpass123_
      - ACCEPT_EULA=Y
    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Testpass123_' -Q "SELECT 1"
      interval: 10s
      retries: 15
    networks:
      - mutual-network
  
  api:
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8080:80"
    depends_on:
      db:
        condition: service_healthy
    networks:
      - mutual-network

networks:
  mutual-network:

同时在Program.cs的app.Run()调用前添加了迁移代码：

using (var scope = app.Services.CreateScope()) {
    var provider = scope.ServiceProvider;

    var context = provider.GetRequiredService<ApplicationDbContext>();

    if (context.Database.GetMigrations().Any()) {
        context.Database.Migrate();
    }
}

此时错误变为：

Cannot open database "testDB" requested by the login. The login failed. Login failed for user 'sa'.

最终解决方案 #

之前在错误的镜像层执行迁移，导致代码无效。通过在本地目录手动创建迁移后再构建镜像，问题得以解决，迁移可在服务启动前执行，数据库正常提供API调用服务。

内容的提问来源于stack exchange，提问作者invalid