求助：用Python连接企业内网中另一台MS SQL Server 2019失败

背景信息 #

• 目标MS SQL Server部署在企业内网另一台机器，确认允许外部连接（实验室设备可正常写入）
• 通过SQL Server Configuration Manager确认目标数据库启用1434端口，netstat显示SQL Browser正监听该端口
• 服务器采用Windows身份验证

测试脚本 #

使用Python的pyodbc库编写测试连接脚本：

import pyodbc as sql

def main():
    print("Let's test!")
    try:
        DB = sql.connect('DRIVER={ODBC Driver 17 for SQL Server}; SERVER=xxx.xxx.xxx.xxx; PORT=1434; DATABASE=DB; Trusted_Connection=yes;')
    except Exception as ex:
        print("It occurred a problem!")
        print("Exception: {0}. Arguments: {1!r}".format(type(ex).__name__, ex.args))
        return 0
    print("It worked! Congrats")
    return 1

if __name__ == '__main__':
    main()

首次错误输出 #

执行脚本后得到错误：

Let's test!
It occurred a problem!
Exception: OperationalError. Arguments: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [2].  (2) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (2)')

初步排查 #

怀疑连接字符串格式有误，尝试过替换SERVER为ADDRESS、使用IP而非实例名等方式，均无效。

补充网络诊断 #

执行tracert发现本地与SQL Server间存在连接超时：

Tracing route to hostname.company.com [xxx.xxx.xxx.xxx]
over a maximum of 30 hops:

  1     7 ms     2 ms     2 ms  routername.company.com [xxx.xxx.xxx.xxx] 
  2     1 ms     1 ms     2 ms  xxx.xxx.xxx.xxx
  3     2 ms     2 ms     2 ms  xxx.xxx.xxx.xxx
  4     2 ms     2 ms     2 ms  xxx.xxx.xxx.xxx
  5     *        *        *     Request timed out.
  6     2 ms     2 ms     2 ms  hostname.company.com [xxx.xxx.xxx.xxx] 

Trace complete.

邻近SQL Server的其他服务器连接正常，且其tracert无超时节点，怀疑是防火墙导致，后续出现超时错误：

It occurred a problem!
Exception: OperationalError. Arguments: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: The wait operation timed out.\r\n (258) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (258)')

求助 #

请分析问题原因并给出解决办法。

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