.NET Core API本地Docker容器无法连接SQL Server问题求助

我有一个.NET Core API + SQL Server组合，在Mac的Visual Studio中运行正常，但将API部署到Docker容器、SQL Server部署到另一个Docker容器后无法正常工作，具体信息如下：

API Dockerfile #

FROM mcr.microsoft.com/dotnet/core/sdk:3.1-buster AS build-env
WORKDIR /TechTalksAPI
COPY NuGet.config ./
COPY TechTalksAPI.csproj ./
RUN dotnet restore
COPY . ./
RUN dotnet publish --configuration Release --output releaseOutput --no-restore

#build runtime image
FROM mcr.microsoft.com/dotnet/core/sdk:3.1-buster

ENV ASPNETCORE_URLS=http://+:58508  
EXPOSE 58508
WORKDIR /TechTalksAPI
COPY --from=build-env /TechTalksAPI/releaseOutput ./
ENTRYPOINT ["dotnet", "TechTalksAPI.dll"]

appsettings.Development.json 文件 #

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=192.168.1.6,1433;Database=TechTalksDB;User Id=sa;Password=Miles1984$$!;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  }
}

项目文件 #

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <!-- <TargetFramework>netcoreapp2.1</TargetFramework> -->
    <TargetFramework>netcoreapp3.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <Folder Include="wwwroot\" />
  </ItemGroup>

  <ItemGroup>
    <!-- <PackageReference Include="Microsoft.AspNetCore.App" /> -->
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.0" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.App" />
  </ItemGroup>

  <ItemGroup>
    <None Include="TechTalksAPI.csproj" />
  </ItemGroup>
</Project>

容器启动命令 #

SQL Server 启动命令 #

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Miles1984$$!' -p 1433:1433 --name sql1 -d mcr.microsoft.com/mssql/server:2019-latest

API 启动命令 #

docker run --name backend -p 58508:58508 application/backend:v1.0

两个容器均运行在默认bridge网络，尝试过自定义网络但仍无效。

终端报错信息 #

Hosting environment: Production
Content root path: /TechTalksAPI
Now listening on: http://[::]:58508
Application started. Press Ctrl+C to shut down.
fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
      An error occurred using the connection to database 'TechTalksDB' on server 'localhost'.
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'TechTalksAPI.Model.TechTalksDBContext'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): 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: TCP Provider, error: 40 - Could not open a connection to SQL Server)
         at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool, SqlAuthenticationProviderManager sqlAuthProviderManager)
         at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
         at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
         at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
         at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
         at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
         at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
         at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
         at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
         at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
         at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
         at Microsoft.Data.SqlClient.SqlConnection.Open()
         at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
         at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
      ClientConnectionId:00000000-0000-0000-0000-000000000000
Microsoft.Data.SqlClient.SqlException (0x80131904): 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: TCP Provider, error: 40 - Could not open a connection to SQL Server)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
ClientConnectionId:00000000-0000-0000-0000-000000000000

核心问题1：环境配置加载错误 #

从报错信息看，API容器运行在Production环境，而你的连接字符串配置在appsettings.Development.json中。.NET Core默认会根据环境变量ASPNETCORE_ENVIRONMENT加载对应配置文件，Production环境下只会加载appsettings.json，不会加载Development的配置，导致API使用的连接字符串不是你配置的那个（可能是默认的localhost连接，这也解释了报错里提到的server是localhost）。

解决方法：

• 方法一：将连接字符串复制到appsettings.json中；
• 方法二：启动API容器时指定环境变量，强制加载Development配置：

  docker run --name backend -e ASPNETCORE_ENVIRONMENT=Development -p 58508:58508 application/backend:v1.0
  

核心问题2：Docker容器间网络访问错误 #

Docker容器默认有独立的网络栈，容器内的localhost指向容器自身，而非宿主机；而你用宿主机IP192.168.1.6访问SQL容器，在默认bridge网络中，容器之间需要通过容器名称或容器IP（不推荐，IP会变）来通信。

解决方法：
修改连接字符串中的服务器地址为SQL容器的名称sql1：

"DefaultConnection": "Server=sql1,1433;Database=TechTalksDB;User Id=sa;Password=Miles1984$$!;"

如果使用自定义网络，同样可以用容器名称访问，无需映射端口到宿主机。

核心问题3：SQL密码的转义问题 #

你启动SQL容器时使用的SA_PASSWORD=Miles1984$$!中，$$在bash shell中会被解析为环境变量，导致实际设置的密码不是Miles1984$$!，而是Miles1984!（丢失了一个$），这会导致API连接时密码错误。

解决方法：
启动SQL容器时用单引号包裹密码（防止shell解析特殊字符），或者转义$符号：

# 方法一：用单引号包裹（bash环境下有效）
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Miles1984$$!' -p 1433:1433 --name sql1 -d mcr.microsoft.com/mssql/server:2019-latest

# 方法二：转义$符号
docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=Miles1984\$\$! -p 1433:1433 --name sql1 -d mcr.microsoft.com/mssql/server:2019-latest

额外优化：Dockerfile使用Runtime镜像 #

你的Dockerfile运行阶段用了sdk:3.1-buster镜像，这会导致镜像体积过大，推荐使用专门的ASP.NET Core Runtime镜像：

FROM mcr.microsoft.com/dotnet/core/sdk:3.1-buster AS build-env
WORKDIR /TechTalksAPI
COPY NuGet.config ./
COPY TechTalksAPI.csproj ./
RUN dotnet restore
COPY . ./
RUN dotnet publish --configuration Release --output releaseOutput --no-restore

# 替换为Runtime镜像
FROM mcr.microsoft.com/dotnet/core/aspnet:3.1-buster

ENV ASPNETCORE_URLS=http://+:58508  
EXPOSE 58508
WORKDIR /TechTalksAPI
COPY --from=build-env /TechTalksAPI/releaseOutput ./
ENTRYPOINT ["dotnet", "TechTalksAPI.dll"]

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