使用ODBC Driver 17 for SQL Server结合AD用户连接MSSQL失败

问题描述 #

在Mac M1设备上，使用FreeTDS驱动、设置authentication="ActiveDirectoryPassword"，通过SQLAlchemy可以正常使用Windows域用户连接MSSQL Server。但在Windows Server 2022服务器上改用ODBC Driver（因登录用户与数据库授权用户不同，无法使用trusted_connection="yes"）时，连接被拒绝，报错连接字符串中的用户名丢失：

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

当前使用的连接字符串创建代码：

from sqlalchemy.engine import URL
from sqlalchemy import create_engine
import socket

if "MacBook" in socket.gethostname():
    driver = "FreeTDS"
else:
    driver = "ODBC Driver 17 for SQL Server"
self.db_api = "pyodbc"

query_dict = {
        "driver": driver,
        "TrustServerCertificate": "yes", 
        "authentication": "ActiveDirectoryPassword" 
    }

connection_url = URL.create(
    f"mssql+{self.db_api}",
    username=self.user,
    password=self.password,
    host=self.server,
    port=self.port,
    database=self.database,
    query=query_dict,
)
self.engine = create_engine(connection_url)
con=Connection(self.engine)

环境信息 #

• Mac环境：Python 3.9.12、SQLAlchemy==1.4.42
• Windows服务器环境：Windows Server 2022 Datacenter（21H2，OS build 20348.1487）、Python 3.9.2、SQLAlchemy==1.4.42

已尝试的无效方案 #

• 更换为ODBC Driver 18 for SQL Server，报错相同
• 将TrustServerCertificate设为"no"，出现SSL证书信任错误：

  OperationalError("(pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.\r\n (-2146893019) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection")

• 不设置认证方式，报错用户登录失败：

  '28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'xxx\xxxx'"

• 设置认证方式为"ActiveDirectoryIntegrated"，报错不能同时使用用户/密码和该认证方式：

  ('FA002', "[FA002] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use Authentication option 'ActiveDirectoryIntegrated' with User or Password options.")

解决方案尝试 #

方案1：直接传递完整ODBC连接字符串 #

SQLAlchemy的URL.create可能会对参数进行转义，导致ODBC驱动无法正确解析用户名。可以直接构建完整的ODBC连接字符串，通过odbc_connect参数传递：

from sqlalchemy.engine import URL
from sqlalchemy import create_engine
import socket

if "MacBook" in socket.gethostname():
    driver = "FreeTDS"
    # Mac下保留原逻辑
    query_dict = {
        "driver": driver,
        "TrustServerCertificate": "yes", 
        "authentication": "ActiveDirectoryPassword" 
    }
    connection_url = URL.create(
        f"mssql+pyodbc",
        username=self.user,
        password=self.password,
        host=self.server,
        port=self.port,
        database=self.database,
        query=query_dict,
    )
else:
    driver = "ODBC Driver 17 for SQL Server"
    # Windows下直接构建ODBC连接字符串
    odbc_conn_str = (
        f"DRIVER={{{driver}}};"
        f"SERVER={self.server},{self.port};"
        f"DATABASE={self.database};"
        f"UID={self.user};"
        f"PWD={self.password};"
        f"Authentication=ActiveDirectoryPassword;"
        f"TrustServerCertificate=yes;"
    )
    connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": odbc_conn_str})

self.engine = create_engine(connection_url)
con=Connection(self.engine)

方案2：确保用户名包含完整域名 #

将域用户格式改为DOMAIN\username（Python中需转义反斜杠，即DOMAIN\\username），确保ODBC驱动能正确识别域用户身份。

方案3：检查ODBC驱动依赖 #

确保Windows Server上已安装Microsoft Authentication Library for SQL Server，该组件是ODBC Driver 17/18支持ActiveDirectoryPassword认证的必要依赖。

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