Docker部署的.NET 7 WebAPI无法连接SQL Server容器数据库求助
环境：Windows 11系统，.NET 7解决方案包含API网关、WebAPI1、WebAPI2（均已Docker化），同时部署了Docker版SQL Server数据库容器。WebAPI连接数据库失败，报错如下：
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)'
错误发生在WebAPI1的Program.cs迁移代码处：
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(); // 错误发生在此处 var identityContext = serviceScope.ServiceProvider.GetRequiredService<IdentityContext>(); identityContext.Database.Migrate(); }
相关代码
连接字符串（尝试过的版本）
版本1：
"ApplicationConnection": "Data Source=127.0.0.1,8001;Initial Catalog=MyDatabase;User Id={username}; Password={password};Trusted_Connection=true;TrustServerCertificate=True"
版本2：
"ApplicationConnection": "Server=127.0.0.1,8001;Database=MyDatabase;User Id={username}; Password={password};Trusted_Connection=true;TrustServerCertificate=True"
数据库上下文
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/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
已排查内容
- 所有容器在Docker Desktop中正常运行
- 可通过SSMS手动连接数据库
- 已允许SQL Server远程连接
- 防火墙已允许SQL Server和Docker通行
解决方案思路
修复连接字符串身份验证冲突
连接字符串同时指定User Id/Password和Trusted_Connection=true，会强制使用Windows集成认证，但容器内无Windows域环境，导致SPN错误。将Trusted_Connection改为false或直接删除该参数，同时将数据源改为Docker服务名（容器间通信无需宿主端口）："ApplicationConnection": "Data Source=database,1433;Initial Catalog=MyDatabase;User Id={username};Password={password};TrustServerCertificate=True"修正SQL Server容器环境变量
官方SQL Server镜像不支持SA_DB_NAME、SA_USER这类自定义变量，正确的SA密码变量是MSSQL_SA_PASSWORD，若需额外用户可使用MSSQL_USER。修正后的database服务环境变量：environment: - ACCEPT_EULA=Y - MSSQL_SA_PASSWORD={password} - MSSQL_USER={username}解决容器启动顺序问题
depends_on仅保证容器启动顺序，不确保SQL Server服务就绪。添加健康检查确保数据库就绪后再启动WebAPI：
在docker-compose.yml的database服务中添加：healthcheck: test: ["CMD-SHELL", "/opt/mssql-tools/bin/sqlcmd -S localhost -U ${MSSQL_USER} -P ${MSSQL_SA_PASSWORD} -Q 'SELECT 1' || exit 1"] interval: 10s timeout: 30s retries: 5 start_period: 30s同时修改WebAPI服务的
depends_on：depends_on: database: condition: service_healthy修正服务名拼写不一致问题
docker-compose.override.yml中WebAPI1的服务名是webapis.webapis1，与docker-compose.yml中的webapis.webapi1拼写不一致，导致环境变量无法生效，统一修正为webapis.webapi1。
内容的提问来源于stack exchange，提问作者ClemTeboll
