Azure虚拟机通过Python连接Azure SQL托管实例超时但本地可正常访问

问题基线 #

• 故障场景：在Azure虚拟机内运行Python脚本，访问xxxx.database.windows.net地址的Azure SQL托管实例数据库，触发连接超时报错
• 已知对照：完全相同的连接配置、相同的驱动版本，在本地个人电脑、非Azure环境的远程服务器上可正常连通，仅Azure虚拟机内连接失败
• 已做的无效尝试：先后更换4个版本ODBC驱动、两种Python连接写法均未解决问题

触发的错误信息 #

OperationalError: ('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.\r\n (10060) (SQLDriverConnect); [08001] [Microsoft][SQL Server Native Client 11.0]Login timeout expired (0); [08001] [Microsoft][SQL Server Native Client 11.0]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. (10060)')

已验证无效的连接尝试 #

连接方式1：SQLAlchemy 集成pyodbc #

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://<username>:<password>@<server>/<database>?driver=SQL+Server+Native+Client+11.0")
query = "select * from table"
df=pd.read_sql(query,engine)

连接方式2：原生pyodbc 手动拼接连接串 #

import pyodbc

server = 'xxx.database.windows.net'
database = 'database'
username = 'username'
password = 'password'   
driver= '{SQL Server Native Client 11.0}'
with pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()

已替换测试的驱动列表 #

{ODBC Driver 11 for SQL Server}
{ODBC Driver 13 for SQL Server}
{ODBC Driver 17 for SQL Server}
{SQL Server Native Client 11.0}

排查修复步骤（按优先级排序） #

1. 网络连通性前置验证
   • 在故障Azure虚拟机内打开PowerShell执行Test-NetConnection -ComputerName xxxx.database.windows.net -Port 1433，如果返回TcpTestSucceeded : False，可直接排除Python驱动、代码逻辑问题，故障在网络层
   • 检查虚拟机关联的网络安全组（NSG）出站规则，确认放通目标为*.database.windows.net、目标端口1433的出站流量
   • 排查虚拟机本地系统防火墙、终端安全软件的拦截规则，确认Python进程没有被限制对外发起1433端口的请求
2. 修正连接字符串典型配置错误
   • 重点踩坑点：Azure SQL托管实例的公网访问端口不是固定1433，是动态分配的端口段，不要在连接串里手动写死PORT=1433，手动指定该参数会直接导致连接被目标端拒绝触发超时，删掉端口配置让驱动自动识别端点端口即可
   • 补充加密配置参数：在连接串中增加Encrypt=Yes;TrustServerCertificate=No，避免驱动默认的加密协商超时
3. 托管实例访问规则校验
   • 如果走公网链路访问托管实例，确认托管实例的公网访问开关已开启，且Azure虚拟机的出站公网IP（经NAT网关、虚拟机公网网卡的出口IP）已加入托管实例的访问白名单
   • 如果走私网链路访问，检查托管实例对应的私有DNS解析配置，确认从Azure虚拟机发起的域名解析能正确指向托管实例的私网IP，同时校验VNet对等连接、路由表配置，确保流量没有被强制牵引到防火墙、网络代理设备造成丢包
4. 链路设备规则校验
   • 如果虚拟机所在VNet部署了Azure防火墙、第三方网络虚拟设备，检查设备规则有没有放通到托管实例端点的出站流量，排除强制拦截导致的超时

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