树莓派Debian 11通过Python连接SQL Server故障求助

环境信息 #

• 设备：树莓派3，运行Debian 11 ARM系统
• 目标数据库：SQL Server 2022 Express
• 已安装组件：unixODBC、pyodbc

初始连接尝试 #

初始连接代码：

import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=myservername\SQLEXPRESS;'
                      'Database=mydatabasename;'
                      'Trusted_Connection=yes;')

抛出错误：

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

尝试手动配置/etc/odbc.ini：

[Name Here]
Description = Description
Driver = /etc/odbcinst.ini
Database = Databasename
Servername = ServerName
UID = Username
PWD = Password

使用该DSN连接时仍报错：

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'Name Here' : file not found (0) (SQLDriverConnect)")

2023年6月26日更新 #

配置/etc/odbcinst.ini：

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

配置/etc/odbc.ini：

[MSSQLServerDatabase]
Driver      = ODBC Driver 17 for SQL Server
Description = Connect to my SQL Server instance
Trace       = No
Server      = *Servername*\SQLEXPRESS

使用sqlserverport检测端口的代码：

# test data
server_name = "*Servername*\SQLEXPRESS"
instance_name = "SQLEXPRESS"

try:
  result = r"Instance {0}\{1} is listening on port {2}.".format(
    server_name,
    instance_name,
    sqlserverport.lookup(server_name, instance_name),
    )
except sqlserverport.BrowserError as err:
    result = err.message
except sqlserverport.NoTcpError as err:
    result = err.message

print(result)

抛出错误：

Traceback (most recent call last):
 File "/home/kshaw/Test.py", line 59, in <module>
 sqlserverport.lookup(server_name, instance_name),
 File "/home/kshaw/.local/lib/python3.9/site-packages/sqlserverport/sqlserverport.py", line 60, in lookup
 sock.sendto(udp_message, (server, udp_port))
 socket.gaierror: [Errno -2] Name or service not known

2023年6月27日更新 #

将server_name改为IP后成功检测到端口：

# Try to connect to SQL Server
server_name = "xxx.xx.xx.xx"
instance_name = "SQLEXPRESS"

try:
    result = r"Instance {0}\{1} is listening on port {2}.".format(
    server_name,
    instance_name,
    sqlserverport.lookup(server_name, instance_name),
    )
except sqlserverport.BrowserError as err:
    result = err.message
except sqlserverport.NoTcpError as err:
    result = err.message

print(result)

输出结果：

Instance xxx.xx.xx.xx\SQLEXPRESS is listening on port 1433.

但使用DSN连接仍报错：

pyodbc.connect('DSN=MSSQLServerDatabase;UID=myuid;PWD=mypwd')

错误信息：

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1' : file not found (0) (SQLDriverConnect)")

排查与解决步骤 #

1. 安装ARM架构适配的ODBC驱动 #

树莓派3为ARM32架构，官方默认提供的msodbcsql17是x86_64版本，直接安装会导致驱动文件路径不匹配。需安装ARM版本：

• 导入微软GPG密钥：

  curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
  

• 添加Debian 11 ARM源：

  curl https://packages.microsoft.com/config/debian/11/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
  

• 更新并安装驱动：

  sudo apt update
  sudo ACCEPT_EULA=Y apt install msodbcsql17
  

  安装完成后，驱动文件路径应为/opt/microsoft/msodbcsql17/libarm/libmsodbcsql-*.so，而非lib64目录。

2. 修正odbcinst.ini中的驱动路径 #

编辑/etc/odbcinst.ini，替换为ARM版本驱动的实际路径（可通过find /opt/microsoft/msodbcsql17 -name "libmsodbcsql-*.so"查找准确路径）：

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

3. 验证驱动注册状态 #

运行以下命令确认驱动是否被unixODBC识别：

odbcinst -q -d

若输出包含[ODBC Driver 17 for SQL Server]，说明注册成功。

4. 调整连接字符串测试 #

直接使用驱动+IP端口的方式连接，避免DSN和身份验证问题：

import pyodbc
conn = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=xxx.xx.xx.xx,1433;'
    'DATABASE=mydatabasename;'
    'UID=myuid;'
    'PWD=mypwd;'
)

注意：Linux环境下默认不支持Windows身份验证，需使用SQL Server本地账号登录。

5. 确认SQL Server端配置 #

• 在SQL Server配置管理器中启用TCP/IP协议；
• 防火墙开放1433端口，允许树莓派IP访问；
• 确保使用的SQL Server账号拥有目标数据库的读写权限。

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