局域网内Pyodbc无法通过FreeTDS连接SQL Server问题及解决

局域网环境下，使用Pyodbc结合FreeTDS连接SQL Server时遭遇上述连接失败错误，尝试更换IP、调整连接字符串参数、切换FreeTDS版本、关闭防火墙等操作均未解决。最终通过修正FreeTDS及ODBC相关配置文件、调整连接字符串成功解决问题。

原始配置与代码 #

原始连接代码 #

import pyodbc, os, sys

currentdir = os.path.dirname(os.path.realpath(__file__))
parentdir = os.path.dirname(currentdir)
sys.path.append(parentdir)

cnxn_str = ("Driver={FreeTDS};",
            "DNS=ganymede;",
            "Port=1433;",
            "Database=players;",
            "Trusted_Connection=yes;",
            "TrustServerCertificate=yes;",
            "TDS_Version=7.1;")

db = pyodbc.connect(cnxn_str)

/etc/hosts配置 #

192.168.1.231 ganymede

原始/etc/odbc.ini配置 #

[ganymede]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Description=ganymede
Trace=No
#TraceFile = /tmp/sql.log
Server=tcp:192.168.1.231
#Port = 1433
#instance=SQLEXPRESS
#Database=players
#TDS_Version = 7.4

原始/etc/odbcinst.ini配置 #

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1
UsageCount=1

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.2.1
UsageCount=1

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so
UsageCount=1

报错栈 #

Traceback (most recent call last):
  File "/media/Dock1/Git/.../sql_migration.py", line 17, in <module>
    db = pyodbc.connect(cnxn_str)
pyodbc.OperationalError: ('08001', '[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

修正后的配置与代码 #

修正后的freetds.conf配置 #

[ganymede]
    host = 192.168.1.231
    port = 1433
    tds version = 7.0

修正后的odbc.ini配置 #

[ganymede]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = ganymede

修正后的odbcinst.ini配置 #

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
CPTimeout =
CPReuse =
FileUsage = 1

修正后的连接字符串 #

Driver={FreeTDS};Server=<FQDN>;UID=<UID>;PWD=<password>;Database=players;TDS_Version=7.0;

关键修改说明 #

• 完善freetds.conf配置，明确指定SQL Server的IP地址、端口号和TDS版本，确保FreeTDS能正确识别并连接目标数据库实例
• 简化odbc.ini配置，通过Servername关联freetds.conf中定义的数据库实例，避免重复配置参数
• 调整odbcinst.ini中FreeTDS驱动的配置，添加CPTimeout、CPReuse和FileUsage参数优化连接管理
• 修改连接字符串：替换Trusted_Connection为账号密码认证（UID和PWD），统一TDS版本为7.0，修正原代码中DNS参数的错误使用

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