MacOS无法连接旧版SQL Server数据库（疑与OpenSSL v3相关）

问题场景 #

MacOS Catalina系统中，因安装新brew导致PHP 7.4损坏，重装PHP及依赖后本地项目运行正常，但无法连接要求TLS v1.0的旧版SQL Server数据库。执行sqlcmd -S <DB_IP>时出现SSL协议不支持错误；尝试降级OpenSSL至1.0后又出现SSPI上下文生成失败错误，恢复至OpenSSL 3.2后，发现程序读取/usr/local/etc/openssl@3/openssl.cnf但修改该文件无效。

已尝试操作 #

• 重装PHP驱动及FreeTDS、unixODBC、msodbcsql17、mssql-tools等brew组件
• 修改/etc/ssl/openssl.cnf无效果
• 降级OpenSSL至1.0触发SSPI错误后恢复至3.2
• 修改/usr/local/etc/openssl@3/openssl.cnf无效果

错误详情 #

OpenSSL 3.2时的错误 #

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSL Provider: [error:0A000102:SSL routines::unsupported protocol].
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.

降级至OpenSSL 1.0时的错误 #

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSPI Provider:  The operation or option is not available.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Cannot generate SSPI context.

环境配置信息 #

unixODBC状态 #

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/MY-USER/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

odbcinst -q -d

[ODBC Driver 13 for SQL Server]
[ODBC Driver 17 for SQL Server]

OpenSSL 3.2 brew安装参数 #

openssl@3 
==> Patching
==> Applying cafccb768be5b8f5c21852764f7b2863b6f5e204.patch
patching file crypto/x509/by_file.c
==> perl ./Configure --prefix=/usr/local/Cellar/openssl@3/3.2.0_1 --openssldir=/usr/local/etc/openssl@3 --libdir=/usr/local/Cellar/openssl@3/3.2.0_1/lib no-ssl3 no-ssl3-method no-zlib darwin64-x86_64-cc enable-ec_nistp_64_gcc_128
==> make
==> make install MANDIR=/usr/local/Cellar/openssl@3/3.2.0_1/share/man MANSUFFIX=ssl
==> make test
==> Caveats
A CA file has been bootstrapped using certificates from the system
keychain. To add additional certificates, place .pem files in
  /usr/local/etc/openssl@3/certs

and run
  /usr/local/opt/openssl@3/bin/c_rehash
==> Summary
🍺  /usr/local/Cellar/openssl@3/3.2.0_1: 6,798 files, 32.5MB, built in 23 minutes 33 seconds
==> Running `brew cleanup openssl@3`...

可行解决方法 #

方法1：修改OpenSSL 3.2配置启用TLSv1.0 #

brew安装OpenSSL3时仅禁用了SSL3，未禁用TLSv1.0，需调整配置降低安全级别并允许旧协议：

1. 编辑/usr/local/etc/openssl@3/openssl.cnf，定位到[system_default_sect]段落，添加或修改以下配置：

   MinProtocol = TLSv1.0
   CipherString = DEFAULT@SECLEVEL=1
   

   （SECLEVEL=1允许使用旧协议和弱加密套件，适配旧版SQL Server）
2. 保存配置后重启终端，执行sqlcmd -S <DB_IP>测试连接

方法2：在ODBC连接字符串中强制指定TLS版本 #

直接在PHP代码或ODBC数据源配置中指定TLSv1.0协议：

• PHP PDO示例：

  $dsn = "sqlsrv:Server=<DB_IP>;Database=<DB_NAME>;Encrypt=YES;TrustServerCertificate=YES;Protocol=TLSv1.0";
  $pdo = new PDO($dsn, $DB_USER, $DB_PASS);
  

• odbc.ini配置示例：
  编辑/usr/local/etc/odbc.ini，添加或修改数据源：

  [OldSQLServer]
  Driver = ODBC Driver 17 for SQL Server
  Server = <DB_IP>
  Encrypt = YES
  TrustServerCertificate = YES
  Protocol = TLSv1.0
  

方法3：重新编译msodbcsql17链接至OpenSSL 1.1 #

OpenSSL 1.1比1.0更兼容现代组件，同时支持TLSv1.0，可重新编译驱动：

1. 卸载现有msodbcsql17：

   brew uninstall msodbcsql17
   

2. 安装openssl@1.1：

   brew install openssl@1.1
   

3. 临时设置环境变量，让brew优先使用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"
   

4. 重新安装msodbcsql17：

   brew install msodbcsql17
   

5. 测试连接后，可恢复原PATH环境变量（若无需全局使用openssl@1.1）

方法4：切换至FreeTDS驱动 #

FreeTDS对旧版SQL Server兼容性更好，可替代ODBC驱动：

1. 确保FreeTDS已安装：

   brew install freetds
   

2. 编辑/usr/local/etc/freetds.conf，添加目标数据库配置：

   [OldSQLServer]
   host = <DB_IP>
   port = 1433
   tds version = 7.0
   client charset = UTF-8
   ssl = require
   check certificate host name = no
   

3. PHP中使用pdo_dblib扩展连接（需确保该扩展已安装）：

   $dsn = "dblib:host=<DB_IP>;dbname=<DB_NAME>";
   $pdo = new PDO($dsn, $DB_USER, $DB_PASS);
   

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