Docker Compose部署下SQL Server的sa用户登录失败问题

尝试在Docker容器中将SQL Server与ASP.NET Core进行连接，即便参考了同类问题的解决方案，仍出现如下错误：

2023-12-20 21:04:49 fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
2023-12-20 21:04:49       An error occurred using the connection to database 'CinemaDatabase' on server 'sql,1433'.
2023-12-20 21:04:49 fail: Microsoft.EntityFrameworkCore.Query[10100]
2023-12-20 21:04:49       An exception occurred while iterating over the results of a query for context type 'Backend.Context.AccountDBContext'.
2023-12-20 21:04:49       Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'sa'.
2023-12-20 21:04:49          at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
2023-12-20 21:04:49          at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
2023-12-20 21:04:49          at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
2023-12-20 21:04:49          at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 

我的docker-compose文件配置如下：

version: "3.8"
services:
  backend:
    build: ./backend
    container_name: backend
    ports:
      - "7238:7238"
    environment:
      - ConnectionStrings__DefaultConnection=Server=sql,1433;Database=CinemaDatabase;User=sa;Password=S3nh@V@lid4;TrustServerCertificate=True;
    depends_on:
      - sql
  sql:
    image: mcr.microsoft.com/mssql/server
    container_name: sql_server
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA="Y"
      - SA_PASSWORD="S3nh@V@lid4"

解决方案 #

• 移除环境变量值的引号
  SQL Server容器的ACCEPT_EULA和SA_PASSWORD环境变量值中的引号会被Docker当作值的一部分，导致实际SA密码为"S3nh@V@lid4"（带引号），与连接字符串中的密码不匹配。修改后的配置如下：

  sql:
    image: mcr.microsoft.com/mssql/server
    container_name: sql_server
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=S3nh@V@lid4
  

• 确保SQL Server服务就绪后再启动后端
  depends_on仅保证容器启动顺序，不代表SQL Server服务已完全就绪。可在后端容器的启动脚本中添加等待逻辑，比如使用wait-for-it.sh检查sql:1433端口是否可用，或在ASP.NET Core项目中实现连接重试机制。

• 验证连接字符串一致性
  确认连接字符串中的Password与SQL Server容器的SA_PASSWORD完全一致，无多余空格、引号或特殊字符转义问题。

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