Pyodbc.connect无法通过创建的登录名和用户连接SQL Server

被这个问题卡了好几天，翻了一堆Stack Overflow帖子和Microsoft、pyodbc官方文档都没解决，应该是有特殊细节没注意到，求帮忙。

目标 #

用Python脚本通过pyodbc连接Docker部署的SQL Server。已编写docker-compose.yml，基于Dockerfile构建镜像，自动执行setup.sql脚本创建数据库、用户及登录名，避免每次启动容器时重复操作。

本地环境与连接代码 #

本地已安装ODBC Driver 17 for SQL Server（通过ODBC数据源管理器确认），使用以下代码连接：

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=kafkaUser;PWD=Kafka_Us3R!;')

注：试过trusted_connection=yes;无效。无论使用sa、kafkaUser还是kafkaLogin及对应密码，都会报错，上述是最后一次尝试的代码。

Dockerfile内容 #

FROM mcr.microsoft.com/mssql/server
WORKDIR /topics

# Env vars for sql server
ENV ACCEPT_EULA Y
ENV MSSQL_SA_PASSWORD <YourStrong!Passw0rd>
ENV MSSQL_PID Developer
ENV MSSQL_HOST localhost
ENV MSSQL_USER kafkaUser
ENV MSSQL_PASSWORD Kafka_Us3R!
ENV MSSQL_DATABASE testdb

EXPOSE 1433:1433

COPY topics/proposal-created-hl/setup.sql setup.sql
COPY topics/proposal-created-hl/setup_database.sh setup_database.sh
COPY topics/proposal-created-hl/entrypoint.sh entrypoint.sh

RUN /opt/mssql/bin/sqlservr & ./setup_database.sh

setup.sql内容 #

-- MSSQL file for local testing with docker container
IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'testdb' )
    CREATE DATABASE [testdb];
GO

USE [testdb]
GO

IF NOT EXISTS ( SELECT name FROM sys.server_principals WHERE name = 'kafkaLogin' )
    BEGIN
        CREATE LOGIN [kafkaLogin] WITH PASSWORD = 'Kafka_Us3R!', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
        ALTER SERVER ROLE [sysadmin] ADD MEMBER [kafkaLogin];
        ALTER LOGIN [kafkaLogin] WITH DEFAULT_DATABASE = [testdb];
        CREATE USER [kafkaUser] FOR LOGIN [kafkaLogin];
    END
GO

报错信息 #

pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'kafkaUser'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'kafkaUser'. (18456)")

setup_database.sh内容 #

#!/usr/bin/env bash
# Wait for database to startup 
sleep 20
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourStrong!Passw0rd>' -i setup.sql

已做排查 #

通过docker exec进入容器确认数据库、用户及登录名已创建，但仍无法连接，尝试多种常见方案均无效，请问遗漏了哪些细节？

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