升级macOS Sonoma后Pyodbc与Pymssql无法连接SQL Server问题

问题描述 #

升级至macOS Sonoma系统后，使用pymssql或pyodbc连接部分SQL Server时失败，但在Docker环境中运行程序可正常工作。当前使用驱动为ODBC Driver 18 for SQL Server，已设置encrypt=no、trust_server_certificate=yes，现有解决方案均为Sonoma发布前版本，需可行建议。

报错信息 #

File "src/pymssql/_pymssql.pyx", line 647, in pymssql._pymssql.connect

File "src/pymssql/_mssql.pyx", line 2109, in pymssql._mssql.connect

File "src/pymssql/_mssql.pyx", line 701, in pymssql._mssql.MSSQLConnection.__init__

File "src/pymssql/_mssql.pyx", line 1818, in pymssql._mssql.maybe_raise_MSSQLDatabaseException

File "src/pymssql/_mssql.pyx", line 1835, in pymssql._mssql.raise_MSSQLDatabaseException

MSSQLDatabaseException: (20017, b'DB-Lib error message 20017, severity 9:\nUnexpected EOF from the server (TheIP)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (TheIP)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (TheIP)\n')

While handling the above exception, another exception occurred:

File "/Users/lewiuberg/TheCompany/TheUser/worker/src/data/connection.py", line 128, in __connection
    65   def __connection(self) -> PyodbcCursor | PymssqlCursor:  # type: ignore
 (...)
    124                          exit(0)
    125  
    126          case "mssql":
    127              try:
--> 128                  cursor = pymssql.connect(  # type: ignore
    129                      host=self.server,
    ..................................................
     self = Connection(server=TheIP, database=TheDatabase, use
             rname=TheUser, password=***, port=1433, driver=ODBC Driver
              18 for SQL Server, encrypt=yes, trust_server_certificate=ye
             s, connection_timeout=300, readonly=False, connection_type=m
             ssql)
     PyodbcCursor = ~PyodbcCursor
     PymssqlCursor = ~PymssqlCursor
     self.server = 'TheIP'
    ..................................................

File "src/pymssql/_pymssql.pyx", line 653, in pymssql._pymssql.connect

可行解决建议 #

• 降级ODBC驱动至17版本：卸载当前的ODBC Driver 18 for SQL Server，安装ODBC Driver 17 for SQL Server，同时修改代码中驱动名称配置，Sonoma对旧版驱动的兼容性更稳定。
• 检查系统防火墙规则：前往「系统设置-网络-防火墙」，确认允许你的Python运行环境（如conda、pyenv管理的Python）出站连接1433端口。
• 重新编译freetds与pymssql：pymssql依赖freetds库，Sonoma系统库变更可能导致旧版本不兼容。执行以下命令重新安装：

  brew reinstall freetds
  pip uninstall pymssql -y
  pip install pymssql --no-binary :all:
  

• 调整pyodbc连接参数：在连接字符串中补充TDS_Version=7.4（适配SQL Server 2017及以上版本），示例：

  conn_str = (
      "DRIVER={ODBC Driver 17 for SQL Server};"
      "SERVER=TheIP;"
      "DATABASE=TheDatabase;"
      "UID=TheUser;"
      "PWD=***;"
      "ENCRYPT=no;"
      "TRUSTSERVERCERTIFICATE=yes;"
      "TDS_Version=7.4"
  )
  

• 适配系统OpenSSL版本：Sonoma自带的OpenSSL可能存在兼容性问题，用Homebrew安装OpenSSL 1.1并配置环境变量，之后重新安装数据库连接库：

  brew install openssl@1.1
  export PATH="/usr/local/opt/openssl@1.1/bin:$PATH"
  export LDFLAGS="-L/usr/local/opt/openssl@1.1/lib"
  export CPPFLAGS="-I/usr/local/opt/openssl@1.1/include"
  pip uninstall pyodbc pymssql -y
  pip install pyodbc pymssql
  

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