Macbook(M2)通过ODBC连接SQL Server 2016失败求助

尝试在VSCode的Jupyter Notebook中查询SQL Server 2016数据库，已按官方说明安装ODBC驱动。运行odbcinst -j可正常输出配置信息，但执行Python代码连接时触发OperationalError。已排查官方文档、重装驱动，且通过Kerberos可直接查询目标服务器，添加TrustServerCertificate=yes配置后仍无法连接。

相关信息 #

odbcinst -j输出 #

unixODBC 2.3.12
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/myname/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Python连接代码 #

import pyodbc
import pandas as pd

servername = 'emp-edwh01'
dbname = 'DWH_CORE'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+servername+';DATABASE='+dbname)
cursor = cnxn.cursor()

query = '''SELECT * limit 100
                 FROM dbp.tab_CMA_CMPositions
        '''

df = pd.read_sql(query, cnxn, index_col='[primary_key]')

初始错误信息 #

OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722 (0) (SQLDriverConnect)')

添加TrustServerCertificate=yes后的错误信息 #

OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)')

完整ODBC日志 #

[ODBC][13195][1728651033.229336][SQLAllocHandle.c][469]
        Entry:
            Handle Type = 2
            Input Handle = 0x7fdf0b3a6400
[ODBC][13195][1728651033.229385][SQLAllocHandle.c][585]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x7fdeeb188e00
[ODBC][13195][1728651033.230136][SQLDriverConnectW.c][298]
        Entry:
            Connection = 0x7fdeeb188e00
            Window Hdl = 0x0
            Str In = [DRIVER={ODBC Driver 18 for SQL Server};SERVER=emp-edwh01;DATABASE=DWH_CORE][length = 74 (SQL_NTS)]
            Str Out = 0x0
            Str Out Max = 0
            Str Out Ptr = 0x0
            Completion = 0
        Threading Level set from Driver Entry in ODBCINST.INI 99 from '99'

        Threading Level set from [ODBC] Section in ODBCINST.INI 0 from '0'

        UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2-INTERNAL'

[ODBC][13195][1728651033.367613][SQLDriverConnectW.c][870]
        Exit:[SQL_ERROR]
[ODBC][13195][1728651033.367677][SQLGetDiagRecW.c][535]
        Entry:
            Connection = 0x7fdeeb188e00
            Rec Number = 1
            SQLState = 0x30a3f4824
            Native = 0x30a3f4818
            Message Text = 0x7fdf0c5f2e00
            Buffer Length = 1023
            Text Len Ptr = 0x30a3f4822
[ODBC][13195][1728651033.367732][SQLGetDiagRecW.c][596]
        Exit:[SQL_SUCCESS]
            SQLState = [08001]
            Native = 0x30a3f4818 -> 0 (32 bits)
            Message Text = [[Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see]
[ODBC][13195][1728651033.367851][SQLFreeHandle.c][324]
        Entry:
            Handle Type = 2
            Input Handle = 0x7fdeeb188e00
[ODBC][13195][1728651033.367882][SQLFreeHandle.c][373]
        Exit:[SQL_SUCCESS]

1. 降级ODBC驱动版本
   ODBC Driver 18默认强制加密连接，与SQL Server 2016的加密配置兼容性较差。卸载当前的Driver 18，安装ODBC Driver 17 for SQL Server，该版本对旧版SQL Server适配性更好。

2. 完善连接字符串参数
   明确启用Kerberos认证，补充加密相关参数，修改连接代码如下：

   cnxn = pyodbc.connect(
       f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={servername};DATABASE={dbname};'
       'Trusted_Connection=yes;Encrypt=no;TrustServerCertificate=yes'
   )
   

   • Trusted_Connection=yes：匹配你已验证的Kerberos访问方式
   • Encrypt=no：避免SQL Server 2016的证书不兼容问题
   • TrustServerCertificate=yes：跳过内部环境的证书链验证

3. 验证服务器名称解析
   终端执行ping emp-edwh01确认名称可解析，若无法解析，在/etc/hosts添加服务器IP与名称映射，或直接用IP替换servername。

4. 检查SQL Server TCP/IP配置
   确认目标SQL Server 2016已启用TCP/IP协议，默认1433端口对外开放，可联系DBA确认配置。

5. 修复SQL语法错误
   SQL Server不支持limit语法，替换为TOP：

   SELECT TOP 100 *
   FROM dbp.tab_CMA_CMPositions
   

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